Issue with finding time difference taking account of weekends and workdays.

[BigGee]

Hello,
I am having trouble calculating a time difference between 2 x calculated dates taking into account workdays / weekends and working hours. Could someone help me coming up with a working formula?
I have tried NETWORKDAYS - but don't know how to account for working hours.

My problem is that I have the following working days
Mon-Thu - 6.30 - 3.30pm with 15min break at 9.30 and 30 min break at 12.30
Fri - 6.30-11.45 with 15min break at 9.30

Would appreciate any help

Thanks

 

[BigGee]

Would this work?:

I also consideres if time difference in negative. If that is never going to happen and if you need the time difference to be a number and not a text, then that is an easy fix.

U2:U3 is formatted as a table named T_Holidays where you can add all your holidays.
result in column G, H, I is formatted as "[h]:mm".

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1				Online Date	Target Offline Date	Actual Offline	Online To Target [h:mm]	Online to Actual [h:mm]	Difference [h:mm]
2				11/09/2024 07:31	13/09/2024 14:59	16/09/2024 08:34	20:29	22:33	2:04			Days	Shift Start	Shift end	Break Start 1	Break end 1	Break Start 2	Break end 2			Holidays
3				11/09/2024 09:17	16/09/2024 10:00	16/09/2024 10:45	21:58	22:43	0:45			Mon-Thu	06:30	15:30	09:30	09:45	12:30	13:00			17/09/2024
4				11/09/2024 11:18	16/09/2024 11:46	16/09/2024 11:46	21:58	21:58	0:00			Fri	06:30	11:45	09:30	09:45
5				16/09/2024 15:25	18/09/2024 06:35	16/09/2024 15:30	0:10	0:05	- 0:05
6				16/09/2024 15:25	18/09/2024 06:35	18/09/2024 06:55	0:10	0:30	0:20
7				16/09/2024 06:00	16/09/2024 16:00		8:15
8				16/09/2024 06:00	17/09/2024 16:00		8:15
9				16/09/2024 06:00	18/09/2024 16:00		16:30
10				16/09/2024 06:00		16/09/2024 16:00		8:15
11				16/09/2024 06:00		17/09/2024 16:00		8:15
12				16/09/2024 06:00		18/09/2024 16:00		16:30
13				16/09/2024 09:25	16/09/2024 09:50	16/09/2024 09:51	0:10	0:11	0:01
14				16/09/2024 12:25	16/09/2024 13:05	16/09/2024 13:06	0:10	0:11	0:01
Sheet1

Cell Formulas
Range	Formula
G2:I14	G2	=LET( timeBetween, LAMBDA(t_1,t_2, LET(n, ROUND((t_2-t_1)*24*60, 0), s, SEQUENCE(n,,t_1, TIME(0,1,0)), d, INT(s), m, VALUE(TEXT(s, "hh:mm")), isWorkingDay, WORKDAY.INTL(d-1, 1, 1, T_Holidays)=d, isWorkingHour, ((WEEKDAY(d, 2)<5)*(m>= $M$3)*(m<$N$3)*NOT((m>=$O$3)*(m<$P$3)+(m>=$Q$3)*(m<$R$3))+(WEEKDAY(d, 2)=5)*(m>= $M$4)*(m<$N$4)*NOT((m>=$O$4)*(m<$P$4)))>0, SUM(isWorkingDay*isWorkingHour)/24/60 )), onlineToTargetOffline, timeBetween(D2, E2), onlineToActualOffline, timeBetween(D2, F2), dif, onlineToActualOffline-onlineToTargetOffline, HSTACK(IFERROR(onlineToTargetOffline, ""), IFERROR(onlineToActualOffline, ""), IFERROR(IF(dif<0, "- ", "") & TEXT(ABS(dif), "[h]:mm"), "")) )
Dynamic array formulas.

Let me know if this works for you.

I am afraid this did not work, everything copied across, I had to change the formatting for columns D to F.
The format wouldnt work for row 6 column B down but did for the others??
No value in Column G,H or I. All the formulas are intact? If I click on G2 it does shadow out H and I2 - perhaps something basic I have done wrong?

 

[felixstraube]

Here is the working file for you to download: TimeDiff.zip

 

[BigGee]

Here is the working file for you to download: TimeDiff.zip

Hello, thank you but my system wont allow me to open the file! - phishing!

 

[BigGee]

Hello, thank you but my system wont allow me to open the file! - phishing!

Actually, I found a way to open this file and it works really well - thank you!

 

[felixstraube]

Glad we could help. Thanks for the feedback.
And I think you should mark the actual post that hast the solution as a solution (post #4).

 

[Joe4]

BigGee,
When marking a post as the solution, please be sure to actually mark the post that contains the solution.
I have updated this thread for you.