+ Reply to Thread
Results 1 to 7 of 7

Calculating Flight Time Per Crew Member

  1. #1
    Registered User
    Join Date
    12-01-2018
    Location
    California, USA
    MS-Off Ver
    2013
    Posts
    5

    Calculating Flight Time Per Crew Member

    I am needing help to calculate flight time based upon if a crew member was present in any seat during the flight. I have been working with the SUMPRODUCT and SUMIFS but keep returning a value of "0". In short, there are 4 positions that a crew member can occupy and if they are in any seat they need to be awarded the flight time. A visual representation of this would be:

    TIME | SEAT A | SEAT B | SEAT C

    1.2 Hr | Person 1 | Person 2 | Person 3
    2.5 Hr | Person 4 | Person 3 | Person 1
    3.8 Hr | Person 1 | Person 5 | Person 2

    Person 1 Total time: "X.X" Hours


    My hope is to build a formula that can tell me how much total flight time each person has logged.

    Thank you in advance!
    Attached Images Attached Images
    Last edited by Raginginferno1; 12-01-2018 at 09:32 AM. Reason: Picture Link Issue

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Calculating Flight Time Per Crew Member

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    3
    seat 1 seat 2 seat 3 seat 4
    4
    8/24/2018
    1
    name 1 name 2 name 3 name 4
    5
    8/25/2018
    2.3
    name 4 name 5 name 6 name 1
    6
    8/26/2018
    7.8
    name 2 name 3 name 4 name 6
    7
    8/27/2018
    4.3
    name 6 name 1 name 2 name 2
    8
    9
    10
    11
    seat 1
    seat 2
    seat 3
    seat 4
    tot
    12
    name 1
    1
    4.3
    0
    2.3
    7.6
    13
    name 2
    7.8
    1
    4.3
    4.3
    17.4
    14
    name 3
    0
    7.8
    1
    0
    8.8
    15
    name 4
    2.3
    0
    7.8
    1
    11.1
    16
    name 5
    0
    2.3
    0
    0
    2.3
    17
    name 6
    4.3
    0
    2.3
    7.8
    14.4



    E12=IF($D12<>"",SUMPRODUCT(($D$4:$G$7=$D12)*($C$4:$C$7)*($D$3:$G$3=E$11)),"") copy across and down

    J12=SUM(E12:H12) copy down

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Calculating Flight Time Per Crew Member

    Please try

    =SUMPRODUCT($C$4:$C$99*($D$4:$G$99="NAME 1"))

  4. #4
    Registered User
    Join Date
    12-01-2018
    Location
    California, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Calculating Flight Time Per Crew Member

    Thank you both very much for your feedback but I do apologies as I left out a very important detail. Seat 1 the instructor position while the other 3 seats are actual positions within the aircraft. This means that it is possible for a person to “occupy” 2 seats during one flight. I have tried Bo_Ty’s formula in the past but the issue I ran into was this will count the flight time twice if I place them in both seats. Is there a work around for this or another approach I can use to maintain an accurate record without doubling the flight time for a person who occupies 2 seats on a single flight?

    Here is a Better Example:

    FLT TIME | SEAT 1 | SEAT 2 | SEAT 1 | SEAT 4

    1.2 Hr | Person 1 | Person 2 | Person 3 | Person 1
    2.5 Hr | Person 4 | Person 3 | Person 1 | Person 2
    3.8 Hr | Person 1 | Person 5 | Person 2 | Person 1

    Person 1 total time: "X.X" Hr

    Thank you so much for your continued help solving this problem. I look foreword to hearing any suggestions.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Calculating Flight Time Per Crew Member

    Maybe

    =SUMPRODUCT($C$4:$C$99*ISNUMBER(SEARCH("Person 1",$D$4:$D$99&E4:E99&F4:F99&G4:G99)))

  6. #6
    Registered User
    Join Date
    12-01-2018
    Location
    California, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Calculating Flight Time Per Crew Member

    Worked Perfectly, Thank you very much for your help!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Calculating Flight Time Per Crew Member

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Flight Crew Scheduling Worksheet
    By T scrilla in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-16-2021, 06:45 AM
  2. Calculating flight hour more than 9999:99
    By perigi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-13-2018, 12:37 PM
  3. Pilot trying to calcualte Night flight time
    By Hernie in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-12-2015, 03:12 PM
  4. How to pull airport flight time from web to excel?
    By andyaf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2013, 09:44 AM
  5. 24 hour lookback - flight time tracker for pilots
    By KenBrown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2012, 07:50 AM
  6. Replies: 2
    Last Post: 01-29-2007, 06:37 PM
  7. [SOLVED] How can I accumulate time in a flight log?
    By RossR in forum Excel General
    Replies: 1
    Last Post: 01-01-2006, 01:55 AM

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