+ Reply to Thread
Results 1 to 6 of 6

Calculating time in Excel

  1. #1
    Registered User
    Join Date
    12-31-2006
    Posts
    1

    Calculating time in Excel

    Hi,

    I need some help constructing a worksheet that will help me calculate time. This spreadsheet will be used to calculate flight time on a rolling 24 hour clock. I know how to convert hours to mins and show the difference between times.

    As an example - here is a what I need to calclulate. A crewmember is only allowed to fly a total of 10 hours in a 24 hour period. The 24 hour clock is a rolling clock.

    A flight departs at 1400 and blocks in at 1630. Blocks back out at 1730 and blocks back in at 2230. The 24 hour clock began at 1400. The total flight time is 7.5 hours. Crew goes into rest and begins flying the next day. They depart at 1030 and block in at 1300. They are still on the same 24 hour clock and now have 10 hours of flight hours. Say they are supposed to depart at 1345 but since they are in the same 24 hour period, they will exceed 10 hours of flight time. I need something set up in excel that will calculate the time in the 24 hour period and tell me when they are legal to depart. In this example, they would be legal to depart at 1400 because they would begin dropping flight time from the previous day (the rolling 24 hour clock). It is possible to drop flight time and accumulate flight without exceeding since it is a rolling clock.

    Anyone out there that can help me with this? I would be forever grateful for any tips on how to set this up! It is sort of difficult to figure on paper and I've been toying with Excel to help me out.

    Anyone may email me directly at skysim70@sbcglobal.net with suggestions.

    Thanks so much!

  2. #2
    Registered User
    Join Date
    06-14-2004
    Posts
    52

    Results

    skysim

    attached is a sample spreadsheet with the possibility of three flights a day as a sample, could be changed to more,. All you are looking for is the next availble departure time based upon no more than 10 hours in any 24 hour period.

    the key here is the If formula
    [ =IF(((B5-A5)*24)+((D5-C5)*24)+((F5-E5)*24)>10,(A5+1-(F6/24)),"OK to Fly"" ] That calculates the flight times add them up and subtracts the next scheduled flight time from the 24 hour perios from the commencement of the 1st flight. you should be ablle to grasp the concept

    see the attached file for the results

    Let me know if this is what you were looking for

    Bud
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-14-2004
    Posts
    52

    Red face Made a mistake


    I made a mistake on the formula for flight time in 24 hours if the next scheduled flight left on time. Fixed on updated version

    see:

    Proof it works 5:This is to show that the Total flight time in 24 hours formula is good, notice the next scheduled flight is 4 days from the last fligh and since the scheduled flight is onl;y 1 hour and the next scheduled flight is 4 days from the last flight. shows the formula works.


    Bud
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-14-2004
    Posts
    52

    Final update to the Flight Log

    I finally got it and it works to with all scenarios I can figure out.

    Only issue is looping one of the macros until the OK_to_Fly notice is up. I need to learn that and also how to prevent a macro from running based upon value in a target cell. But see the attached file and evaluate.
    Attached Files Attached Files
    Last edited by whisperinghill; 01-04-2007 at 10:56 PM. Reason: Major mistakes in sample

  5. #5
    Registered User
    Join Date
    04-22-2015
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    1

    Re: Calculating time in Excel

    I know this goes back a while, but was there ever an update to this file? I'm experiencing some issues with the file, but for the most part it works nicely. It seems cell F6 might be missing something? What I see in it is #REF*24.

  6. #6
    Registered User
    Join Date
    06-14-2004
    Posts
    52

    Re: Calculating time in Excel

    HTML Code: 

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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