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
1	start	Target	Actual	# of workday hours
2	8/31/2017 9:00	9/5/2017 9:00	9/4/2017 15:00	54

<tbody>
</tbody>

Sheet13​

Worksheet Formulas Cell Formula B2 =WORKDAY(A2,3)+MOD(A2,1) D2 =(NETWORKDAYS(A2,C2)-1+MOD(C2,1)-MOD(A2,1))*24 <tbody> </tbody>

<tbody>
</tbody>

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.