+ Reply to Thread
Results 1 to 5 of 5

time formula with fixed travel time

  1. #1
    Registered User
    Join Date
    12-31-2008
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2003
    Posts
    1

    time formula with fixed travel time

    Let's see if I can express this in a way that makes sense...

    I'm trying to put together a formula that will allow me to put my arrival time in one cell, a fixed travel time in a second cell, and have a time of day to leave for the destination calculated in a third cell. Does that make sense to anyone but me?

    Thanks in advance for your help.

    A

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    if Arrival time is in A2 and travel time in B2, both in time format, then try this formula in C2

    =MOD(A2-B2,1)

    format C2 as a time

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    A1 has your arrival time. (6:00 PM)
    B1 has an integer for minutes (45)

    C1 =A1-(B1/1440) Result: 5:15 PM
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    12-30-2008
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2003
    Posts
    64
    If A2 is arrival time and B2 is travel time *in minutes*, you can use:

    =TIME(HOUR(A2),MINUTE(A2)-B2,0)

    If travel time is in hours, e.g. 1.5, you can use:

    =TIME(HOUR(A2),MINUTE(A2)-ROUND(B2*60,0),0)

    The TIME function is smart enough to know what to do with a negative figure for the [minute] argument.

  5. #5
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Thread moved to correct forum!
    Not all forums are the same - seek and you shall find

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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