+ Reply to Thread
Results 1 to 9 of 9

Creating a travel formula

  1. #1
    Registered User
    Join Date
    10-21-2012
    Location
    LONDON
    MS-Off Ver
    Office 365
    Posts
    7

    Creating a travel formula

    Hello Guys

    I am looking for help. I want to create an airport time formula. What i want to achieve we have clients who book airport transfers and constantly calling the office asking what time should they book they car for. So i wanted to create something that they could enter their flight departure time and depending on what airport, you would get a value which would be the estimated set off time. For example: Flying from Manchester airport at 16:00, you know you need to be there 2 hours before which is 14:00, you are then travelling from london, which you know is about 4 hour drive, so the system would say your setoff time would be appoximately 10am.

    Many Thanks[/LIST]

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,300

    Re: Creating a travel formula

    Very simply

    =Departure_Time - Check_in_Time - Estimated_Travel time

    e.g. 16:00 - 02:00 - 04:00

    Data required:

    List of Airports

    Check-In times (National vs Inernational)
    .
    Estimated travel times between destinations : for large town/ cities you could use existing mileage charts available and use estimated average speed.


    Put together a "prototype" workbook and then come back if more help is required.

  3. #3
    Registered User
    Join Date
    10-21-2012
    Location
    LONDON
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Creating a travel formula

    Tried it, just get #Value

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,300

    Re: Creating a travel formula

    Format all cells including result, as hh:mm

    in D1 =A1-B1-C1

    A1=Dept time
    B1=Check in
    C1=journey time

  5. #5
    Registered User
    Join Date
    10-21-2012
    Location
    LONDON
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Creating a travel formula

    Thank you, it doesn't feel, like what im trying to achieve is possible. On the journey time, wanted to make it very simple, for instance, if journey time was 1 hour you would enter a 1 or 60mins and it would be calculated. As trying to figure out the journey time at present is difficult and returns a value of #############

  6. #6
    Registered User
    Join Date
    10-26-2015
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    36

    Re: Creating a travel formula

    see attached file for a simple yet affective calculator, only fill in the blank spots
    Attached Files Attached Files
    Thinking outside of the box by drawing circles.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating a travel formula

    Quote Originally Posted by ukman2012 View Post
    returns a value of #############
    When dealing with times the "hash" marks typically represent a negative time and by default Excel will not display negative time values.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Registered User
    Join Date
    10-26-2015
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    36

    Re: Creating a travel formula

    Quote Originally Posted by Tony Valko View Post
    When dealing with times the "hash" marks typically represent a negative time and by default Excel will not display negative time values.
    this happens because the complete format also enters in a date (and that is 1/1/1900) that has a value of 1 or 0 (not sure about it) and bases every calculation with this date. as excel cannot process dates earlier than this day

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating a travel formula

    Quote Originally Posted by stielo View Post
    this happens because the complete format also enters in a date
    That's another possibility. In that case just widen the column!

    (and that is 1/1/1900) that has a value of 1 or 0 (not sure about it)
    1/1/1900 has the numeric value of 1. Enter that date then change the cell format to General or Number and you'll see the numeric value.

    1/0/1900 has the numeric value of 0.

+ 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. [SOLVED] Formula to show Direction of Travel with an Arrow
    By IN_FOR_SIN in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-12-2015, 10:17 AM
  2. what formula i can use for mt travel agency DB.
    By SalgotraSonia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-19-2015, 03:40 AM
  3. Travel Scheduler
    By sead16 in forum Excel General
    Replies: 10
    Last Post: 08-27-2014, 04:42 AM
  4. formula to add/deduct an hour for travel [solved]
    By unclejambo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2013, 10:06 AM
  5. time formula with fixed travel time
    By loudguy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2009, 06:33 AM
  6. travel time distance formula
    By kapanenship in forum Excel General
    Replies: 2
    Last Post: 05-20-2008, 12:35 PM
  7. travel expenses by month
    By silky_green in forum Excel General
    Replies: 0
    Last Post: 08-22-2006, 12:00 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