Calculating working hours between two dates

[Bubuto]

How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8 - 17), and there is a weekend between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.

regards,
Petteri Toukoniitty

 

[matt_symes]

Apologies - had transcribed this badly from Excel formula. Have made some corrections below:

work_hours_in_day is just 24*(work_end_time - work_start_time)

=(NETWORKDAYS(start_date_time,end_date_time)*work_hours_in_day)-IF(WEEKDAY(start_date_time,2)>=6,0,MIN(MAX(24*(MOD(start_date_time,1)-work_start_time),0),work_hours_in_day)) - IF(WEEKDAY(end_date_time,2)>=6,0,MIN(24*MAX((work_end_time-MOD(end_date_time,1)),0),work_hours_in_day))

 

[Cristian226]

How this Thread don't have any feedback, I came to thank. This solution works very well!

Thank you Matt!

 

[jahli]

Hi Matt,

I would like to adapt this formula to our work environment but am having trouble.

Those that initiate the work are in the US and those that complete the work are in India so I think we will have to use a 24 hour day but exclude weekends and holidays.

Example data:
Case Status Start Date/Time End Date/Time
1393250 Closed 5/16/14 6:54 5/19/14 3:34
1389755 Closed 5/15/14 15:45 5/15/14 16:01
1389726 Closed 5/15/14 15:34 5/16/14 12:19
1389560 Closed 5/15/14 14:34 5/16/14 5:23
1389279 Closed 5/15/14 13:13 5/16/14 9:48
1387441 Closed 5/15/14 6:10 5/15/14 10:16
1383604 Closed 5/14/14 16:24 5/15/14 9:08
1383517 Closed 5/14/14 15:39 5/15/14 8:19
1383397 Closed 5/14/14 15:01 5/19/14 11:05

 

[jahli]

All times are the US local time for the location where the case was initiated.

 

[jahli]

P.S. And am using Excel 2010 on Win7.