Hours Between 2 Dates Excluding Non-Business Hours ONLY

[j0eyjedi]

Can someone please help me calculate the hours between 2 dates, excluding non-business hours ONLY.

I do not believe the "NETWORKDAYS" function will work in this example as I do not want to exclude weekends, only non-business hours are excludable.

Example:
Excludable Non-Business Hours = 10pm - 9am
Start Time = 9/19/24 9:00 pm
End Time = 9/20/24 9:00 am

The result should be 1 hour (9pm-10pm = 1 hour and all other non business hours are excluded)

I've tried several formulas using median and mod, but I'm stuck.

Any help is greatly appreciated!

 

[myall_blues]

Here's a couple of different options depending on how you have input your date/time.

j0eyjedi.xlsx
	A	B	C	D	E	F
1	Start date	Start time	End date	End time
2	19/09/2024	21:00	20/09/2024	9:00		1
3	19/09/2024 21:00		20/09/2024 9:00			1
Sheet1

Cell Formulas
Range	Formula
F2	F2	=((C2-A2)*("22:00"-"9:00")+D2-B2)*24
F3	F3	=((INT(C3)-INT(A3))*("22:00"-"9:00")+TIME(HOUR(C3),MINUTE(C3),SECOND(C3))-TIME(HOUR(A3),MINUTE(A3),SECOND(A3)))*24

 

[j0eyjedi]

Here's a couple of different options depending on how you have input your date/time.

j0eyjedi.xlsx
	A	B	C	D	E	F
1	Start date	Start time	End date	End time
2	19/09/2024	21:00	20/09/2024	9:00		1
3	19/09/2024 21:00		20/09/2024 9:00			1
Sheet1

Cell Formulas
Range	Formula
F2	F2	=((C2-A2)*("22:00"-"9:00")+D2-B2)*24
F3	F3	=((INT(C3)-INT(A3))*("22:00"-"9:00")+TIME(HOUR(C3),MINUTE(C3),SECOND(C3))-TIME(HOUR(A3),MINUTE(A3),SECOND(A3)))*24

Hey Murray,

Thank you for your help!

I entered your formula and it returned 1 when the format of the column was a "Number", but when i formatted the column to display as "Hours" (13:30) the result was incorrect.

I attached a screenshot below. Column (J) shows the result that I would like to have calculated in column (G)

Any help is appreciated!

 

[j0eyjedi]

Here's a couple of different options depending on how you have input your date/time.

j0eyjedi.xlsx
	A	B	C	D	E	F
1	Start date	Start time	End date	End time
2	19/09/2024	21:00	20/09/2024	9:00		1
3	19/09/2024 21:00		20/09/2024 9:00			1
Sheet1

Cell Formulas
Range	Formula
F2	F2	=((C2-A2)*("22:00"-"9:00")+D2-B2)*24
F3	F3	=((INT(C3)-INT(A3))*("22:00"-"9:00")+TIME(HOUR(C3),MINUTE(C3),SECOND(C3))-TIME(HOUR(A3),MINUTE(A3),SECOND(A3)))*24

In this screenshot column G is formatted as "Number" and column H is formatted as "Time (13:30)"

 

[felixstraube]

Another opcion:

Column A and B formatted as "dd/mm/yyyy hh:mm"
Column C formatted as "[h]:mm"

TimeDiff.xlsx
	A	B	C
1	Start	End	Difference [h:mm]
2	11/09/2024 21:00	12/09/2024 09:00	1:00
3	11/09/2024 21:00	12/09/2024 10:00	2:00
4	20/09/2024 21:00	23/09/2024 09:00	27:00
Sheet1 (2)

Cell Formulas
Range	Formula
C2:C4	C2	=LET( t_1, A2, t_2, B2, n, ROUND((t_2-t_1)*24*60, 0), s, SEQUENCE(n,,t_1, TIME(0,1,0)), m, VALUE(TEXT(s, "hh:mm")), isWorkingHour, (m>= VALUE("9:00"))*(m<VALUE("22:00")), res, SUM(isWorkingHour)/24/60, res )

 

[myall_blues]

I entered your formula and it returned 1 when the format of the column was a "Number", but when i formatted the column to display as "Hours" (13:30) the result was incorrect.

What do you mean the result was ‘incorrect’. A result of 1 hour will never format as 13:30 - it will format as ‘01:00’.
Can you also explain your calculations for row 5 in your images, or perhaps it’s just a mistake? Your working day is from 09:00 to 22:00 (13 hours), so for row 5:
Commencing 01:00 on the 16th will be 13 hours on that day plus another 13 for each of the 17th and 18th respectively, then 1 hour 10 minutes on the 19th gives (13*3)+1:10=40:10, but column K says 27:10?

 

[j0eyjedi]

Another opcion:

Column A and B formatted as "dd/mm/yyyy hh:mm"
Column C formatted as "[h]:mm"

TimeDiff.xlsx
	A	B	C
1	Start	End	Difference [h:mm]
2	11/09/2024 21:00	12/09/2024 09:00	1:00
3	11/09/2024 21:00	12/09/2024 10:00	2:00
4	20/09/2024 21:00	23/09/2024 09:00	27:00
Sheet1 (2)

Cell Formulas
Range	Formula
C2:C4	C2	=LET( t_1, A2, t_2, B2, n, ROUND((t_2-t_1)*24*60, 0), s, SEQUENCE(n,,t_1, TIME(0,1,0)), m, VALUE(TEXT(s, "hh:mm")), isWorkingHour, (m>= VALUE("9:00"))*(m<VALUE("22:00")), res, SUM(isWorkingHour)/24/60, res )

That worked! thank you so much!

 

[felixstraube]

Glad we could help. Thanks for the feedback.