Calculating business hours between two dates

[Jayden831]

I need to calculate response times in business hours either same day or between 2 days. Business hours are M-F 8-8 and Sat 930a-1230p. I don't care about holidays and response time hours could be greater than 24 hours. I need to deliver h:mm result only counting business hours.

a1 start date
b1 start time
c1 end date
d1 end time

i was able to get it somewhat working using network days formula but obviously didn't account for Saturday and/or didn't work for any result greater than 24 hours. The h:mm result can be a whole number or decimal as long as it calculates total hours.

Please help. I have spent hours trying different combinations from the boards and can't seem to get this right.

Desperate. Thanks

 

[iliace]

I have not arrived at a solution, but I did a lot of experimentation, and perhaps my learnings could get you close to the answer.

Take a look:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Weekday From	08:00
2	Weekday To	20:00
3	Saturday From	09:30
4	Saturday To	12:30
5
6	Start Date	Start Time	End Date	End Time		Day After Start	Day Before End	Hours In-Between		Start Date Hours	End Date Hours	Total Hours
7	8/3/2015	10:00	8/7/2015	15:00		8/4/2015	8/6/2015	1.50		2:00:00	7:00:00	45:00:00
Sheet1

 

[Jayden831]

 

[Jayden831]

Thanks Iliace. I am going to play with your approach now as it is very different. If you have a minute can you take a look at my formula? I need to add in Saturday hours 930-1230 and then also know how to deliver results higher than 24 hours. Any help would be greatly appreciated. Thanks!the

 

[iliace]

I think there might be a problem with the images, they aren't loading for me.

 

[Jayden831]

Try this. Thanks

Date TimeSent Date Time Response
Sent Sent Received Received Bus Hrs Notes

08/01/15 0:37 AM 08/01/15 2:10 PM 0:00 Should be 4:05 (a Saturday)
08/03/15 10:22 AM 08/07/15 2:22 PM 4:00 Should be 46 (roughly) * Over 24 hours
08/03/15 12:58 PM 08/04/15 1:12 PM 12:14 ok
08/03/15 3:12 PM 08/05/15 1:38 PM 22:26 ok
08/03/15 5:08 PM 08/03/15 7:32 PM 2:24 ok
08/03/15 6:17 PM 08/03/15 8:12 PM 1:43 ok
08/03/15 10:06 PM 08/04/15 1:11 PM 5:11 ok
08/04/15 10:02 AM 08/06/15 6:37 PM 8:35 Should be 32.5 (roughly) * Over 24 hours

=(NETWORKDAYS(A2+B2,F2+G2)-1)*("20:00"-"8:00")+IF(NETWORKDAYS(F2+G2,F2+G2),MEDIAN(MOD(F2+G2,1),"8:00","20:00"),"20:00")-MEDIAN(NETWORKDAYS(A2+B2,A2+B2)*MOD(A2+B2,1),"8:00","20:00")


[TABLE="width: 767"]
<colgroup><col><col><col span="2"><col><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

I believe this task is complex. Take a look at this link, which might be helpful: http://www.exceltactics.com/calculate-net-work-hours-using-networkdays/
<strike></strike>