+ Reply to Thread
Results 1 to 9 of 9

Calculating Turnaround Time

  1. #1
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    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. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    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. #3
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    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. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    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. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    Please Login or Register  to view this content.
    Format as [h]:mm
    Attached Files Attached Files

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    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. #9
    Registered User
    Join Date
    06-01-2014
    Posts
    79

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Computing turnaround time down to minutes/seconds
    By hypothetical in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-16-2014, 05:25 PM
  2. Turnaround time with work hours - IT Help Desk
    By egiovia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-18-2013, 02:36 PM
  3. Help computing turnaround time for night shift
    By hypothetical in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2013, 10:37 AM
  4. [SOLVED] Working out Turnaround Time based on three dates/times
    By david1987 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-11-2012, 12:54 PM
  5. [SOLVED] Calculate turnaround time for 9pm-6am working time & Start/End date may be on weekend
    By Wauiwa in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-26-2012, 02:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1