+ Reply to Thread
Results 1 to 4 of 4

Calculating range of time with max time constraint.

  1. #1
    Registered User
    Join Date
    04-06-2011
    Location
    Hawaii
    MS-Off Ver
    Excel 2007
    Posts
    2

    Calculating range of time with max time constraint.

    I'm trying to have Excel compute Post Mission Crew Rest for aviators. We are allowed after 16 hours of being away 1 hour for every 3 hours away not to exceed 96 hours. I want to then add that date and time to the individual's return date and time (C10 and D10). Thank you in advance for your help!!!

    Example:
    B C D
    7 Mission Takeoff Time 21-Mar 23:00
    8 Mission Land Time 28-Mar 23:00
    9 Mission Duration-Hours 168:00
    10 PMCR Date/Time 31-Mar 7:00
    Last edited by PhiloBeddoe; 04-06-2011 at 06:30 PM. Reason: Wrong Title

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Calculating range of time with max time constraint.

    Question: You say after 16 hours, you are allowed one hour off for every three, yet your example gives one hour off for every three starting from the very first hour. So does it count one hour for every three as long as the mission is over 16 hours or is it one hour for every three starting with the 17th hour? Also, if there is, say, two hours extra, does the person get forty minutes added to their time or is it just nixed?
    Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.

    If you like a post, please rate it with the scales icon (top right).
    If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.

  3. #3
    Registered User
    Join Date
    04-05-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Calculating range of time with max time constraint.

    Fellow aviator, I can help you with this.

    Your first step is to calculate the total time (in hours) you were gone, which it looks like you have already done.
    For C9: =(C8-C7)*24

    Two steps for C10. Find the amount of PMCR. There are two options. The first is if the trip is less than 288 hours (96 hours*3). The other is just 96 hours (4 days). Excel natively calculates everything in days, so a little bit of conversion is needed.

    For C10: =IF(C9<288,C8+(C8-C7)/3,C8+4)

    You really don't even need C9 if you don't want it.

    You can just replace the formula for C9 in C10, for =IF((C8-C7)<12,C8+(C8-C7)/3,C8+4)

    Format for date/time.

  4. #4
    Registered User
    Join Date
    04-06-2011
    Location
    Hawaii
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Calculating range of time with max time constraint.

    milChuck,

    Thank you for the solution. Is there a way to indicate that the mission was less than 16 hours in the solution? I know that the crew dawg can tell, but to meet the purpose of this calculator I would like it to be fool proof. Thank you again for the help!!!!!!!!!!!!!!!!!

    Philo

+ 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