SOLVED

XL

Copper Contributor

Hi - I recently imported my Flight Logbook with 30 years of aeroplane flight data into an XL file.

How do I calculate the total hours and minutes of flying totals when the flight goes through midnight?

i.e. through 24:00 hours...

6 Replies

Assuming flights do not exceed 24hrs then

= MOD( EndTime - StartTime, 1 )

should do it. More generally you would need to use the date/time in which the integer part represents the date and the fractional part is the time.

Hi Peter
Thanks for your reply. I wrote the formula that you suggested but it simply calculated 00.00 when applied... any ideas?
Scrub my last message. It was just my ineptitude. It works! Thanks :folded_hands:
Try this:
=(EndTime+(EndTime<StartTime)-StartTime)*24

@Twifoo 

 

Thanks.  Peters formula works - 99.9% of my hours are rounded to the nearest 5 mins but there is a cell that isn't somewhere in the totals column - how do I spot it/them to round them please? Already tried the search function for 8 in a highlighted column...

best response confirmed by BEN_FAWKES (Copper Contributor)
Solution

@BEN_FAWKES , in general rounding to nearest 5 min is as

=MROUND(A1,"0:05")
1 best response

Accepted Solutions
best response confirmed by BEN_FAWKES (Copper Contributor)
Solution

@BEN_FAWKES , in general rounding to nearest 5 min is as

=MROUND(A1,"0:05")

View solution in original post