Turnaround Time between date & time needed in hours excluding weekends and holidays

[dawwni]

I need to calculate total turn around time between a start date and time and an end start and time. The turnaround time would be reported in total hours between the two and need to exclude weekends and holidays.
Example:

Date Received	Time Received	Date Completed	Time Completed	Turnaround Time in Hours
1/3/23	8:26	1/4/23	8:26
1/3/23	13:15	1/4/22	16:33

Please also clarify the cell formats in order for it to work. Thanks,

 

[awoohaw]

cell format should be [hh]:mm

Try this formula in cell I2. I did not do anything else with the other formulas in the worksheet:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date Received	Time Received	Date Completed	Time Completed	whole days	whole days in hrs	TAT Hrs (Turn Around Time)	combined	Calculated Hours					Holidays
2	Mon, 2023-03-13	06:20	Tue, 2023-03-14	08:20	00:00	00:00	00:00	00:00	26:00		Should be 26 hrs			2023-01-01
3	Mon, 2023-03-13	06:20	Tue, 2023-03-14	08:22	00:00	00:00	00:48	00:48	26:02		Should be 26 hr 2 min			2023-05-29
4	Mon, 2023-03-13	06:20	Tue, 2023-03-14	08:49	00:00	00:00	11:36	11:36	26:29					2023-07-04
5	Mon, 2023-03-13	06:20	Tue, 2023-03-14	08:22	00:00	00:00	00:48	00:48	26:02					2023-09-04
6	Mon, 2023-03-13	06:20	Tue, 2023-03-14	08:50	00:00	00:00	12:00	12:00	26:30					2023-11-23
7	Mon, 2023-03-13	06:20	Tue, 2023-03-14	08:51	00:00	00:00	12:24	12:24	26:31					2023-11-24
8	Mon, 2023-03-13	10:00	Tue, 2023-03-14	10:18	00:00	00:00	07:12	07:12	24:18					2023-12-24
9	Mon, 2023-03-13	06:20	Tue, 2023-03-14	08:51	00:00	00:00	12:24	12:24	26:31					2023-12-25
10	Mon, 2023-03-13	10:05	Tue, 2023-03-14	09:23	00:00	00:00	07:12	07:12	23:18		Formulas
11	Mon, 2023-03-13	10:05	Tue, 2023-03-14	09:23	00:00	00:00	07:12	07:12	23:18		Whole Days	2
12	Mon, 2023-03-13	09:50	Tue, 2023-03-14	08:51	00:00	00:00	00:24	00:24	23:01		Whole Days inHrs	48
13	Tue, 2023-03-14	14:41	Wed, 2023-03-15	07:35	00:00	00:00	21:36	21:36	16:54		TAT in Hrs	2
14	Tue, 2023-03-14	13:30	Wed, 2023-03-15	11:24	00:00	00:00	21:36	21:36	21:54		Combined	50
15	Tue, 2023-03-14	13:30	Wed, 2023-03-15	11:23	00:00	00:00	21:12	21:12	21:53
16	Fri, 2023-03-03	16:00	Mon, 2023-03-06	08:00	00:00	00:00	00:00	00:00	16:00		not working at all over a weekend
Sheet1

Cell Formulas
Range	Formula
L11	L11	=NETWORKDAYS.INTL(A2,C2,1,N2:$N9)+IF(B2>D2,-1,0)
L12	L12	=24*(NETWORKDAYS.INTL(A2,C2,1,O2:$U9)+IF(B2>D2,-1,0))
L13	L13	=(IF(B2>D2,1,0)+D2-B2)*24
L14	L14	=24*(NETWORKDAYS.INTL(A2,C2,1,O2:$U9)+IF(B2>D2,-1,0))+(IF(B2>D2,1,0)+D2-B2)*24
I2:I16	I2	=IF(NETWORKDAYS.INTL(A2,C2,1,$N$2:$N$9)-1=0,D2-B2,(NETWORKDAYS.INTL(A2,C2,1,$N$2:$N$9)-1)) +(D2-B2)