Formula for calculating business hours between 2 date/times

[JenkinsM82]

I'm trying to create a formula that will calculate the number of business hours between two date/times. The start of business hours is 8:00 am and the end is 5:00 pm. Plus, I need to take holidays into consideration. In these examples, 1/2/2023 is in the "Holidays" list.

Example	Start Time (A1)	End Time (B1)	# of Hours
A	1/4/2023 1:30:00 PM	1/6/2023 3:30:00 PM	20.00
B	1/2/2023 5:06:00 AM	1/3/2023 9:00:00 AM	1.00
C	1/2/2023 9:18:00 AM	1/3/2023 8:19:00 AM	0.32
D	1/3/2023 10:00:00 AM	1/4/2023 9:00:00 AM	8.00

Here is the formula I'm using:

=(NETWORKDAYS.INTL(A1, B1, "0000000", Holidays)-1) * 9 + MAX(MIN(MOD(B1,1), TIMEVALUE("17:00")) - MAX(MOD(A1,1), TIMEVALUE("08:00")), 0) * 24

This formula works for examples A and B but example C results in 0 and example D results in 9.

Anyone have any ideas on how to fix this?

 

[vladimiratanasiu]

I'm trying to create a formula that will calculate the number of business hours between two date/times. The start of business hours is 8:00 am and the end is 5:00 pm. Plus, I need to take holidays into consideration. In these examples, 1/2/2023 is in the "Holidays" list.

Example	Start Time (A1)	End Time (B1)	# of Hours
A	1/4/2023 1:30:00 PM	1/6/2023 3:30:00 PM	20.00
B	1/2/2023 5:06:00 AM	1/3/2023 9:00:00 AM	1.00
C	1/2/2023 9:18:00 AM	1/3/2023 8:19:00 AM	0.32
D	1/3/2023 10:00:00 AM	1/4/2023 9:00:00 AM	8.00

Here is the formula I'm using:

=(NETWORKDAYS.INTL(A1, B1, "0000000", Holidays)-1) * 9 + MAX(MIN(MOD(B1,1), TIMEVALUE("17:00")) - MAX(MOD(A1,1), TIMEVALUE("08:00")), 0) * 24

This formula works for examples A and B but example C results in 0 and example D results in 9.

Anyone have any ideas on how to fix this?

Try it, after you defined the holidays in a range:

Book1
	A	B	C	D
1	Start Time	End Time	Holidays	Hours / minutes
2	1/4/2023 13:30	1/6/2023 15:30		20:00
3	1/2/2023 5:06	1/3/2023 9:00	1/2/2023	1:00
4	1/2/2023 9:18	1/3/2023 8:19		0:19
5	1/3/2023 10:00	1/4/2023 9:00		8:00
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=(NETWORKDAYS.INTL(A2,B2,11,C$2:C$5)-1)*("17:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,11,C$2:C$5),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,C$2:C$5)*MOD(A2,1),"8:00","17:00")