Need a formula for Business Hours Mon-Fri 8AM - 5PM between two dates and times!!

[catterz66]

I desperately need a formula ASAP for calculating the working hours of 8AM - 5PM between a start date and end date, that doesn't count any hours outside of this range (which means no weekend hours).

For example if I had these two dates 12/04/24 10:50AM and 15/04/24 2:15PM, I would want to see only the hours that are in between that range. I know how to calculate the total hours between the two, but I can't restrict it down to not exclude hours outside a specific date and time range.

Please if anyone can help, need this formula within the next two days !

 

[etaf]

how about
=(NETWORKDAYS(B5,C5)-1)*($G$3-$G$2)
+IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),$G$3,$G$2),$G$3)
-MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),$G$3,$G$2)

where
G2 is the lower time 08:00
and G3 is the upper time 17:00

Book13
	A	B	C	D	E	F	G	H
1
2							8:00
3							17:00
4
5		1/6/24 5:00	1/8/24 10:00		2:00:00		Sat	Mon
6		4/12/24 10:50	4/15/24 14:15		12:25:00		Fri	Mon
7
8
9		6:10	6:15
Sheet1

Cell Formulas
Range	Formula
G5:H5,G6	G5	=B5
H6	H6	=C5
E5:E6	E5	=(NETWORKDAYS(B5,C5)-1)*($G$3-$G$2) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),$G$3,$G$2),$G$3) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),$G$3,$G$2)

If we take your example
12/04/24 10:50AM and 15/04/24 2:15PM,
thats 6hrs 10min on the friday
and 6hrs 15 min on the monday
total = 12hr 25 min

format the results cell in [H]:MM to show hours above 24

formula from

Get work hours between dates and times

To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function. In the example shown, E5 contains this formula: =(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper)...

exceljet.net

 

[catterz66]

That worked perfectly when i added the 0800 and 1700 as their own values in g2 and g3 ! thankyou

 

[etaf]

you are welcome