Weekday Turn-Around Time Calculations

[MOCCOM]

I have a need to calculate two distinct attributes, by formulas (not VBA). Please note I am using UK date formats here.

1. Target Sign-Off Time. This should be calculated by adding weekday hours e.g. 24, 48, 72 to a date-time stamp in Excel. The logic needs to exclude weekend hours.
2. TAT Result. This should be calculated by comparing the Assigned Time (Start) to Actual Sign-Off Time (Stop), to determine the number of weekday hours actually consumed.

Example:
Assigned Time: 31/08/2017 09:00
TAT Target (Hrs): 72 (essentially 3 whole weekdays)
Target Sign-Off Time: Solution needed that outputs as 05/09/2017 09:00, in example given
Actual Sign-Off Time: 04/09/2017 15:00
TAT Result: Solution needed that outputs as 54 - that is 54 hrs (hh)

Any help gratefully received!

 

[Eric W]

Try:

	A	B	C	D
start	Target	Actual	# of workday hours

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8/31/2017 9:00[/TD]
[TD="align: right"]9/5/2017 9:00[/TD]
[TD="align: right"]9/4/2017 15:00[/TD]
[TD="align: right"]54[/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=WORKDAY(A2,3)+MOD(A2,1)[/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=(NETWORKDAYS(A2,C2)-1+MOD(C2,1)-MOD(A2,1))*24[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The WORKDAY function in B2 requires you to enter a whole number of days, which seems like what you want to do. For the # of workday hours, I used NETWORKDAYS for the whole days, and calculated the difference for the hours. I use MOD in several places to handle the times.

I used American style dates, but it should work fine with UK style.

 

[MOCCOM]

Thank you Eric! This has me tantalizingly close. Is there any way to format the output to hh:mm i.e. 54:00, and ideally still allowing calculations to use this value, and not formatting as text?

 

[Eric W]

Well, you could take off the *24 from the D2 formula, then format the cell > More Number formats... > Time > 37:30:55, and that will show 54:00:00. You can still use D2 in calculations, but you'd need to multiply it by 24 to get the hours again.