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]

What are your standard weekend days? And Holidays are usually maintained in a table, so if you could give me an example of a few coming up for you it would be great, if not I'll assume Sat/Sun and just make up a holiday list.

 

[awoohaw]

mr excel questions 12.xlsm
	A	B	C	D	E	F	G
2	Date Received	Time Received	Date Completed	Time Completed	Turnaround Time in Hours
3	2001-03-23	08:26	2001-04-23	08:26	504.000		Holiday List
4	2001-03-23	13:15	2001-04-22	16:33	483.300		2001-03-27
5
Sheet17

Cell Formulas
Range	Formula
E3:E4	E3	=24*NETWORKDAYS.INTL(A3,C3,1,$G$4)+((D3-B3)*24)

 

[awoohaw]

The format in columns A, C, and G are YYYY-MM-DD.
Columns B & D are HH:MM:SS.

Column is decimal. the decimal portion is the decimal portion of an hour.

The holiday date is input into the 4th argument of the NetWorkdays.INTL function. If you add more holidays you will need to expand that part of the formula.
Using 24 hour time makes this a little easier. But, please test it out especiallally in instances where the finish date is a monday or day after holiday and the time is earlier than the start time. I'm not super certain of the various scenarios.

It is also counting full days. If you want only 8 hour days then you need to change the formula in column E by changing the 24 multiplier at the front to 8.

 

[awoohaw]

The format in columns A, C, and G are YYYY-MM-DD.
Columns B & D are HH:MM:SS.

Column is decimal. the decimal portion is the decimal portion of an hour.

The holiday date is input into the 4th argument of the NetWorkdays.INTL function. If you add more holidays you will need to expand that part of the formula.
Using 24 hour time makes this a little easier. But, please test it out especiallally in instances where the finish date is a monday or day after holiday and the time is earlier than the start time. I'm not super certain of the various scenarios.

It is also counting full days. If you want only 8 hour days then you need to change the formula in column E by changing the 24 multiplier at the front

as I think on this i need to still adjust the hour component formula.

 

[awoohaw]

well, it looks like the math took care of it all, so the above seems accurate. Here is my comparison. It may not be as thorough as you want. So please test to your satisfaction:

mr excel questions 12.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2	Date Received	Time Received	Date Completed	Time Completed	whole days w adj	Whole days in hours w adj	hrs decimal w adj	combined w ajd	whole days w no adj	whole days in hrs w no adj	hours decimal w no adj	Turnaround Time in Hours no adj
3	Fri 2001-03-23	08:26	Mon 2001-04-23	08:26	21	504.00000	0.0000	504.00000	21	504.00000	0.0000	504.00000
4	Fri 2001-03-23	13:15	Mon 2001-04-23	10:00	20	480.00000	20.7500	500.75000	21	504.00000	-3.2500	500.75000
5	Fri 2001-03-23	8:00 AM	Mon 2001-04-23	08:00	21	504.00000	0.0000	504.00000	21	504.00000	0.0000	504.00000
6	Fri 2001-03-23	9:00 AM	Mon 2001-04-23	15:00	21	504.00000	6.0000	510.00000	21	504.00000	6.0000	510.00000
7	Fri 2001-03-23	10:00 AM	Fri 2001-04-27	09:00	24	576.00000	23.0000	599.00000	25	600.00000	-1.0000	599.00000
8	Fri 2001-03-23	11:00 AM	Fri 2001-04-27	08:00	24	576.00000	21.0000	597.00000	25	600.00000	-3.0000	597.00000
9	Fri 2001-03-23	12:00 PM	Fri 2001-04-27	15:00	25	600.00000	3.0000	603.00000	25	600.00000	3.0000	603.00000
10	Fri 2001-03-23	1:00 PM	Fri 2001-04-27	09:00	24	576.00000	20.0000	596.00000	25	600.00000	-4.0000	596.00000
11	Fri 2001-03-23	2:00 PM	Fri 2001-04-27	12:00	24	576.00000	22.0000	598.00000	25	600.00000	-2.0000	598.00000
12	Fri 2001-03-23	3:00 PM	Tue 2001-05-01	08:00	26	624.00000	17.0000	641.00000	27	648.00000	-7.0000	641.00000
13	Fri 2001-03-23	4:00 PM	Tue 2001-05-01	15:00	26	624.00000	23.0000	647.00000	27	648.00000	-1.0000	647.00000
14	Fri 2001-03-23	5:00 PM	Tue 2001-05-01	09:00	26	624.00000	16.0000	640.00000	27	648.00000	-8.0000	640.00000
Sheet17

Cell Formulas
Range	Formula
E3:E14	E3	=NETWORKDAYS.INTL(A3,C3,1,$N$4)+IF(B3>D3,-1,0)
F3:F14	F3	=24*(NETWORKDAYS.INTL(A3,C3,1,$N$4)+IF(B3>D3,-1,0))
G3:G14	G3	=(IF(B3>D3,1,0)+D3-B3)*24
H3:H14	H3	=24*(NETWORKDAYS.INTL(A3,C3,1,$N$4)+IF(B3>D3,-1,0))+(IF(B3>D3,1,0)+D3-B3)*24
I3:I14	I3	=NETWORKDAYS.INTL(A3,C3,1,$N$4)
J3:J14	J3	=24*NETWORKDAYS.INTL(A3,C3,1,$N$4)
K3:K14	K3	=((D3-B3)*24)
L3:L14	L3	=24*NETWORKDAYS.INTL(A3,C3,1,$N$4)+((D3-B3)*24)
C4:C6,C8:C11,C13:C14,A5:A14	C4	=C3
C12	C12	=C11+4

 

[dawwni]

Wow this looks amazing, thank you. I will test it out today!

 

[dawwni]

So it looks like the very first formula should work but instead of getting 24 hours for 1 day, it is calculating 48 hours. See 1st row and row 10 should be 3 hours. Do I have too many *24 in there? We want 24 hours per day, not 8 for just work hours.

In addition, we do get partials "delayed" so we calculate a separate TAT for those like in row 5.

QTY	Date Received	Time Received	Date Completed	Time Completed	Date Partial Received	Time Partial Received	# of Partial Samples	TAT in Hrs	Partial TAT in Hrs	Holidays
2	2023-01-03	8:26	2023-01-04	8:26				48.00​		2023-01-01​
8	2023-01-03	13:15	2023-01-04	16:33				51.30​		2023-05-29​
35	2023-01-04	9:30	2023-01-05	14:07				52.62​		2023-07-04​
1	2023-01-04	na	2023-01-08	9:00	2023-01-04	10:30	1	#VALUE!​	70.50​	2023-09-04​
2	2023-01-04	10:00	2023-01-05	7:00				45.00​		2023-11-23​
1	2023-01-04	20:00	2023-01-05	9:00				37.00​		2023-11-24​
4	2023-01-04	9:00	2023-01-04	13:00				28.00​		2023-12-25​
10	2023-01-04	7:00	2023-01-04	10:00				27.00​		2023-12-26​

Formula is 24*NETWORKDAYS.INTL(date received,date completed,1,$Holiday List)+((Time completed-time received)*24)

or =24*NETWORKDAYS.INTL(B2,D2,1,$K2:$K9)+((E2-C2)*24)

this is using 1 for Sat & Sun, if I wanted to include Fridays in the weekend I would use 7 instead, correct?

I just can't figure out why my base is 48 hours not 24? Thanks in advance.

 

[dawwni]

Also, I think it would be better if I wrote an IF statement for the Partials using the "# of Partials" column. Count IF H1>1, or am wrong on this assumption?

 

[awoohaw]

Also, I think it would be better if I wrote an IF statement for the Partials using the "# of Partials" column. Count IF H1>1, or am wrong on this assumption?

I will crunch your latest table with what I have in my copy of the workbook. Will let you know.

As far a partials... can you explain how the math will work with that? Is it just that time start is different, or is there another indicator to delay the hours counting?