Calculate Revert Time

[surevyas1984]

Team,
Please help me with the formula, formula is entered in TAT (in hours)
Mon to Fri from 7AM to 5PM and Sat and Sun 8AM to 12:30PM. There is also holiday list. TAT (In H:MM) . Formula should calculate difference between Received Date & Time & Sent Time. It should calculate time of working hours only and should not consider non-working hours.
In case Holiday is updated in Public Holidays & work hours under column B then TAT should not calculate on that day.
Shift time and Holiday is updated in sheet "Public Holidays & work hours".

User	Date	Bkg No.	BLNo.	Task	Location	Received Date & Time	Sent DATE & TIME	Tat (In Hours)
Rajesh Wagh	06-08-2023	VASDXB2300960	VASJEACOK010709	BL	AEJEA	06-08-2023 13:23	06-08-2023 13:23	0:00
Krishna Mudaliyar	16-08-2023	VASDXB2300980	VASJEANSA010746	BL	AEJEA	15-08-2023 19:38	16-08-2023 09:54	0:00
Krishna Mudaliyar	19-08-2023	BALDXB2300037	BLTJEAKHI010753	BL	AEJEA	19-08-2023 12:26	19-08-2023 12:53	0:00
Krishna Mudaliyar	26-08-2023	DXBMT2308169	GMAEJEAIXY010787	Empty BL	AEJEA	26-08-2023 09:59	26-08-2023 10:31	0:00

=SUM((NETWORKDAYS.INTL(H14,I14,1,holid)-1)*(out-in)+IF(NETWORKDAYS.INTL(I14,I14,1,holid),MEDIAN(MOD(I14,1),out,in),out)-MEDIAN(NETWORKDAYS.INTL(H14,H14,1,holid)*MOD(H14,1),out,in),(NETWORKDAYS.INTL(H14,I14,"1111111",holid)-1)*(sout-sin)+IF(NETWORKDAYS.INTL(I14,I14,"1111111",holid),MEDIAN(MOD(I14,1),sout,sin),sout)-MEDIAN(NETWORKDAYS.INTL(H14,H14,"1111111",holid)*MOD(H14,1),sout,sin))

Public Holidays & work hours Sheet

Occasion	Date			Work start time (in)	07:00 AM		Mon-Fri
Holiday1	09 January 2023			Work end time (Out)	05:00 PM
Holiday2
Holiday3				Work start time (Sin)	08:00 AM		Sat & Sun
Holiday4				Work end time (Sout)	12:30 PM

Looking forward for your support

 

[felixstraube]

How about this?:

Calculate Revert Time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	User	Date	Bkg No.	BLNo.	Task	Location	Received Date & Time	Sent DATE & TIME	Tat (In Hours)			Work starting hour	Work ending hour
2	Rajesh Wagh	06/08/2023	VASDXB2300960	VASJEACOK010709	BL	AEJEA	06/08/2023 dom 12:25	07/08/2023 lun 08:00	1:05		Mon-Fri	07:00	17:00
3	Krishna Mudaliyar	16/08/2023	VASDXB2300980	VASJEANSA010746	BL	AEJEA	15/08/2023 mar 19:38	16/08/2023 mié 09:54	2:54		Sat-Sun	08:00	12:30
4	Krishna Mudaliyar	19/08/2023	BALDXB2300037	BLTJEAKHI010753	BL	AEJEA	19/08/2023 sáb 12:26	19/08/2023 sáb 12:53	0:04
5	Krishna Mudaliyar	26/08/2023	DXBMT2308169	GMAEJEAIXY010787	Empty BL	AEJEA	26/08/2023 sáb 09:59	26/08/2023 sáb 10:31	0:32		Holidays
6							31/12/2023 dom 12:00	02/01/2024 mar 09:00	2:30		01/01/2024
7							31/12/2023 dom 12:00	08/01/2024 lun 09:00	47:00		06/01/2024
Hoja1

Cell Formulas
Range	Formula
I2:I7	I2	=IFERROR(LET(t,H2-G2, n_min,ROUND(t*24*60,0), m,SEQUENCE(n_min,,G2,TIME(0,1,0)), workingMinute_Holiday,DATE(YEAR(m),MONTH(m),DAY(m))=WORKDAY.INTL(m-1,1,"0000000",Holidays), h,m-INT(m), workingMinute_Hour, IF(WEEKDAY(m,2)<6,(h>=$L$2)*(h<$M$2),(h>=$L$3)*(h<$M$3)), SUM(workingMinute_Holiday*workingMinute_Hour)/24/60 ),0)

Named Ranges
Name	Refers To	Cells
Holidays	=Hoja1!$K$6:$K$22	I2:I7

 

[surevyas1984]

Dear felixstraube,
I would appreciate if you could help me with what name have to defined so that i can check whether it is meeting the requirement. as I dont have an access to XL2BB

 

[felixstraube]

Hi.
Just copy with this button:

and paste into a new sheet in cell A1.


But anyways for the formula in I2 you ranges are here as you can see in the formula. The only named range as you had is "Holidays" in K6:K22.

Calculate Revert Time.xlsx
	G	H	I	J	K	L	M
1	Received Date & Time	Sent DATE & TIME	Tat (In Hours)			Work starting hour	Work ending hour
2	06/08/2023 do 12:25	07/08/2023 lu 08:00	1:05		Mon-Fri	7:00	17:00
3	15/08/2023 ma 19:38	16/08/2023 mi 09:54	2:54		Sat-Sun	8:00	12:30
4	19/08/2023 sá 12:26	19/08/2023 sá 12:53	0:04
5	26/08/2023 sá 09:59	26/08/2023 sá 10:31	0:32		Holidays
6	31/12/2023 do 12:00	02/01/2024 ma 09:00	2:30		01/01/2024
7	31/12/2023 do 12:00	08/01/2024 lu 09:00	47:00		06/01/2024
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Hoja1

Cell Formulas
Range	Formula
I2:I7	I2	=IFERROR(LET(t,H2-G2, n_min,ROUND(t*24*60,0), m,SEQUENCE(n_min,,G2,TIME(0,1,0)), workingMinute_Holiday,DATE(YEAR(m),MONTH(m),DAY(m))=WORKDAY.INTL(m-1,1,"0000000",Holidays), h,m-INT(m), workingMinute_Hour, IF(WEEKDAY(m,2)<6,(h>=$L$2)*(h<$M$2),(h>=$L$3)*(h<$M$3)), SUM(workingMinute_Holiday*workingMinute_Hour)/24/60 ),0)

Named Ranges
Name	Refers To	Cells
Holidays	=Hoja1!$K$6:$K$22	I2:I7

 

[felixstraube]

Here is a working file:
Calculate Revert Time.zip

 

[surevyas1984]

Dear felixstraube,
Thank you for this formula, I have checked the file and formula is giving me the required result It is calculating Sat and Sun working hours as well with in and out time defined , Also It is excluding holiday date as well.. that is super.
Once again Thank you

Regards
Suresh Vyas

 

[felixstraube]

Happy to help. Thanks for the feedback.