# Calculating Turnaround Time

1. ## Calculating Turnaround Time

I would like to know how to approach it. This could be an easy calculation.

This is for a trucking company and I want to calculate how many hours an asset has been idol. I have the dates an times of use for each asset on a tracking sheet. Each row contains a date when the load was picked up (along with a time) and a date/time it was delivered. I want another column to tell me how many hours have elapsed between the current "time in staging" from the last "time out of location".

Turnaround Time.xlsx

I have attached a file with the column (Column "S") that I would like to populate itself

I suspect it will be a match and index formula

Any help is greatly appreciated.

Thanks

2. ## Re: Calculating Turnaround Time

I don't think you need any lookup, actually, since you're comparing values inside a row, right?

First let's talk about the format that Excel uses to store date & time info.

DATE is an integer count of days starting with Day 1 as Jan 1st, 1900 -- the "1900" date standard.

That means no matter how your dates are formatted to display, as long as they are stored as those integer values, you can simply do DATE_LATER - DATE_EARLIER = difference in days.

TIME is a value between 0.00 starting at midnight and wrapping around to about 0.99 at 11:59 PM (so noon is 0.50 for example).

This means that you can have a cell that has both DATE+TIME info; the integer is the date, and the 'fractional' number is time.

So all you need to do is this:
TAT = (DATE_OUT + TIME_OUT) - (DATE_IN + TIME_IN)
and then use a custom cell format like
[h]:mm
to display everything in hours:minutes; the brackets on the [h] tell it to wrap past 24 hours, so rather than a day and a half, it would display 36:00 for "36 hours, zero minutes".

3. ## Re: Calculating Turnaround Time

Thanks for the quick reply. I understand that I need to combine the times and dates. I believe I can do that with a couple helper columns that I will later hide. What I'm confused about is how to match that number to a previous Date and time for the same truck.

4. ## Re: Calculating Turnaround Time

Use the filters that are part of the table headers. Click on the down arrow for the Truck ID and select the truck, you can also filter on the dates at the same time.

Right now, your example only shows the date of Pickup but no time.

I have added a calculation in column R (I inserted a new column) that has a calculation to combine the date and time. (highlighted in yellow)

ben_hensel's calculation description is correct. Add the time required for the receipt so that you can create the date and time like I show in column R then apply the formula that ben_hensel outlined for you.

5. ## Re: Calculating Turnaround Time

Thank you.
I might not be making myself clear or maybe I'm missing the point so I'll try to explain it in a different way.

Turnaround Time.xlsx

I have combined pick up date/time and delivery date/time in separate columns (T and U). Each pickup and delivery time correlates to one Truck. I need to have the formula search for all the entries, in columns T and U, given the corresponding truck number and find the shortest time (in hours) between the last delivery time/date and the current pick up time/date.

For example... TT01 was last delivered on 6/2/2014 at 3:30 AM. If I put in an entry for TT01 being picked up at 6/3/2014 at 3:00 AM I want the turnaround time to display 23.5 (6/2/2014 at 3:30 AM - 6/3/2014 at 3:00 AM). So basically for every entry it should search the same truck then look at the delivery date/time prior to it and subtract it from the current pickup date/time.

I appreciate all the help

6. ## Re: Calculating Turnaround Time

The "Turn Around Time" as I understand the term would be the Delivery Date/time - Pickup Date/time . The calculations produced are vastly different from what you show so I don't know how those times were arrived at.

Formula:
`Please Login or Register  to view this content.`
Format as [h]:mm

7. ## Re: Calculating Turnaround Time

Last delivery to current pickup:

 M S T U V 3 Truck # TAT [hrs] Pick Up Date/Time Deliver Date/Time 4 TT 03 6/1/14 12:30 AM 6/1/14 1:15 AM S4 and down: =IFERROR((T4 - LOOKUP(2, 1/(M\$3:M3 = M4), U\$3:U3)) * 24, "") 5 TT 01 6/2/14 2:00 AM 6/2/14 3:30 AM 6 TT 02 6/2/14 4:30 AM 6/2/14 7:30 AM 7 TT 04 6/2/14 3:00 AM 6/2/14 4:30 AM 8 TT 03 51.25 6/3/14 4:30 AM 6/3/14 6:30 AM 9 TT 01 23.50 6/3/14 3:00 AM 6/3/14 6:15 AM 10 TT 02 21.00 6/3/14 4:30 AM 6/3/14 6:00 AM 11 TT 04 50.00 6/4/14 6:30 AM 6/4/14 7:15 AM 12 TT 03 26.00 6/4/14 8:30 AM 6/4/14 1:30 PM

8. ## Re: Calculating Turnaround Time

I want to show the time an asset has been idol. So the time since it was last delivered to the time it is called upon again. My mistake if I confused the turnaround time with idol time

9. ## Re: Calculating Turnaround Time

Yes! this looks promising. I won't be by my computer for little but when I'm back ill check it. Thank you!

There are currently 1 users browsing this thread. (0 members and 1 guests)