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

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".

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.

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

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

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