Results 1 to 2 of 2

Thread: Need Help Plwase

  1. #1

    Need Help Plwase



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need to calculate how many pairings can operate to contractual and regulatory requirements.



    CAP rules are, a Pilot can be on duty at 0600 Local and be off duty by 2359 Local

    Contract rules are, a Pilot can be on at 0800 Local and be off duty by 2100 Local.



    A pilot is on duty 01:15 prior departure time off flight

    A pilot is off duty 00:30 after arrival time off flight.



    I need to break down the report as I only need departures out of Base, BHX|LTN|LGW|EMA|LBA|MAN and arrivals into the same baseBook1.xlsx

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Either use pivot tables or countifs. However both methods have problems.

    Your time is text not time, you can change this to time with the timevalue function. However the countif(timevalue(STA), ">=" &Cap_st) fails with this. I tried sumproduct and a host of others.

    Likewise running a pivot table and using a calculated field to determine CAP/Control fails (returns 1 or 0).

    What I suggest is adding two columns,
    CAP =IF(TIMEVALUE(I12) >= Cap_s,IF(TIMEVALUE(I12) <= Cap_e,1,0),0) and
    Control =IF(TIMEVALUE(I12) >= Contract_s,IF(TIMEVALUE(I12) <= Contract_e,1,0),0)

    Then doing a normal pivot table to filter the departing and arriving stations

Posting Permissions

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