Find the time difference

[ExcelNewbie2020]

Find the difference in minutes excluding the weekends and out of working time

example, ticket was created Friday at 7:50 pm and was completed Monday at 8:20.

Work starts at 8am and ends at 8pm

The result should be 30min (10 min from 7:50pm and 20min to 8:20am

 

[lrobbo314]

This seems to do it.

EXCEL
	A	B	C
1	5/10/2024 19:50		30
2	5/13/2024 8:20
Sheet2

Cell Formulas
Range	Formula
C1	C1	=LET(dif,(A2-A1)*1440+1,dt,SEQUENCE(dif,,A1,1/24/60),dow,WEEKDAY(dt,2),h,HOUR(dt),SUMPRODUCT((dow<6)*(h>=8)*(h<20))-1)

 

[ExcelNewbie2020]

This seems to do it.

EXCEL
	A	B	C
1	5/10/2024 19:50		30
2	5/13/2024 8:20
Sheet2

Cell Formulas
Range	Formula
C1	C1	=LET(dif,(A2-A1)*1440+1,dt,SEQUENCE(dif,,A1,1/24/60),dow,WEEKDAY(dt,2),h,HOUR(dt),SUMPRODUCT((dow<6)*(h>=8)*(h<20))-1)

it works.. thank you