Calculate Turn Around Time 7 days working excluding Holiday and in Working Hours

[surevyas1984]

Dear Team,
I have a scenario where team is working 7 days. Mon to Fri from 7AM to 5PM and Sat and Sun 8AM to 12:30PM. There is also holiday list.
Please help me Create a formula in BL Draft sheet under column K header 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".

BL Draft Sheet

Sr. No.	User	Date	Bl No	Bkg No.	SGSIN/MYPKG	Invoice Generated	Invoice Sent	Received Date & Time	Sent Time	Tat (In H:MM)
1	Alex	04-01-2023	VASPKLNML008223	VASPKG2202697	MYPKG	No	No	29-12-2022 14:30	04-01-2023 16:37
2	Alex	07-01-2023	VASPKLCCU008240	VASPKG2202689	MYPKG	No	No	07-01-2023 06:54	07-01-2023 09:15
3	Alex	09-01-2023	GMAEPKGTAO002696	GMLPKG2200620	MYPKG	No	No	08-01-2023 07:39	08-01-2023 12:41
4	Alex	09-01-2023	VASPKLKHI008243	VASPKG2202672	MYPKG	No	No	09-01-2023 07:50	09-01-2023 15:23
5	Alex	09-01-2023	VASPKLKHI008244	VASPKG2202672	MYPKG	No	No	09-01-2023 07:50	09-01-2023 15:23

Public Holidays & work hours Sheet

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

Looking forward for your support

Regards
Suresh Vyas

 

[surevyas1984]

I tried using the below formula
=SUM((NETWORKDAYS.INTL(H7,I7,1,holid)-1)*(out-in)+IF(NETWORKDAYS.INTL(I7,I7,1,holid),MEDIAN(MOD(I7,1),out,in),out)-MEDIAN(NETWORKDAYS.INTL(H7,H7,1,holid)*MOD(H7,1),out,in),(NETWORKDAYS.INTL(H7,I7,"0000000",holid)-1)*(sout-sin)+IF(NETWORKDAYS.INTL(I7,I7,"0000000",holid),MEDIAN(MOD(I7,1),sout,sin),sout)-MEDIAN(NETWORKDAYS.INTL(H7,H7,"0000000",holid)*MOD(H7,1),sout,sin))


Considering Out is "5.00PM" from Mon and Fri, In is "7.00AM" from Mon to Fri.
Sin and S out is for Sat and Sun.
Holid is for holiday list

Please help if Anything that I am missing

 

[surevyas1984]

Can someone plz help me

 

[surevyas1984]

No Response received