Formula for Date/time to calculate turnaround time

[Abell]

Hi Everyone,

Looking to create a formula to calculate the turnaround time based on the time/date in and time/date out. And if possible an easy way to pull the data from the turnaround column into the following timeslot columns 1-4hrs, 4-8hrs, 8-24hrs, greater than 24 hours.

I have attached an example

Thank you for the help.

[63falcondude]

Try this in E2:

=(D2+C2)-(B2+A2)
Formatted as Custom [h]:mm

I don't understand what you are expecting for columns F through I.

[63falcondude]

If you want to put an x into the correct column for each row, you can do that like this:

1 to 4 hours (I would change this to 0 to 4):
F2 =IF(AND(E2>=TIME(1,0,0),E2<=TIME(4,0,0)),"x","")
If you change this to 0 to 4, replace TIME(1,0,0) with 0.

4 to 8 hours:
G2 =IF(AND(E2>TIME(4,0,0),E2<=TIME(8,0,0)),"x","")

8 to 24 hours:
H2 =IF(AND(E2>TIME(8,0,0),E2<=1),"x","")

greater than 24 hours:
I2 =IF(E2>1,"x","")

[FlameRetired]

I don't understand the columns F:I part either.

Please post another example with results hand typed in for a few rows (4 or 5) the output you want there.

[ron2k_1]

Does this help?

In the duration buckets ("1-4", 4-8", etc), you can change what you want the IF statement to show if it is TRUE. This requires that you understand IF formulas. Right now I told the formula to give the duration turnaround time again.

[FlameRetired]

Since numbers are much easier to work with times and dates (rather than text "times" and "dates") I changed the turn around back to what 63falcondude did, then used format manager to format all outputs as d "days" h "hours" m "minutes".

In F2 and down this formula.

Please Login or Register  to view this content.