Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Time calculation (XP)

    Writing this spreadsheet and one of the calculations is arrival time from one flight and the departure time of another.
    In cell B:21 I have the arrival time.
    In cell F20 I have the departure time.
    In cell F24 I have =(MINUTE(E24))+(HOUR(E24)*60) and in E25
    =IF(F24<=120,"transfer time could create problems","")

    My problem is cell E24. All I need to do is extract the departure time from the arrival time. I've tried all sorts of IF formulas but it just doesn't work

    We need more then 2 hours transfer time, that's why the message "transfer time could create problems" comes up in red when its less then 120 minutes.

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time calculation (XP)

    Hi Karat

    I am trying to simulate your description in an excel worksheet and getting a circular reference.

    Is there a chance you can add a sample workbook to the next post so I can see how you have it layed out please
    Jerry

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Time calculation (XP)

    Have attached the section. As most of the flight info is linked, I have copies and paste special the values

    When I need is a formula that works

    I've tried and tried and it just won't work.

    Thanks so much for your help
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time calculation (XP)

    I have had a look and understanding you have stripped some data out I added some data manually to A2 and D2 to simulate an arrival and departurs date and it seems to be working. I also changed the format of E24 to Time/Date and it seems to match
    Jerry

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Time calculation (XP)

    It doesn't work. I have to use the 12 hour clock. No-one here uses the 24 hour clock, so if I use 15:00 I will be asked to change that to 3pm.

    I think the am/pm is my problem.

    Thanks

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time calculation (XP)

    Got you, then we can easily adjust my previous workbook <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    You just need to change the format of of the cells. Highlight cell A2 , B2 and E24 seperately and change the format of the cells by clicking

    Format --> Cells and then highlight the Custom category and type in h:mm AM/PM in the type field.

    Now when you add times stipulate 4:00 AM and this will tell excel that it is 0400 if you type 4:00 PM it will be 1600.

    HTH
    Jerry

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Time calculation (XP)

    Your formula really helped. I deleted all the times and re-entered them setting the format 12 the 12 hour clock and entered them as 24 hour clock

    So thanks for the formula.. It works now

    Thanks again

Posting Permissions

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