• 544K Members
• 6,955 Online
• 649K Conversations
SOLVED

## XL

Highlighted
Occasional Contributor

# XL

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
Highlighted

# RE: XL

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.

Highlighted

# Re: RE: XL

Hi Peter
Thanks for your reply. I wrote the formula that you suggested but it simply calculated 00.00 when applied... any ideas?
Highlighted

# Re: RE: XL

Scrub my last message. It was just my ineptitude. It works! Thanks 🙏
Highlighted

# Re: XL

Try this:
=(EndTime+(EndTime<StartTime)-StartTime)*24
Highlighted

# Re: XL

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...

Highlighted
Solution

# Re: XL

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

`=MROUND(A1,"0:05")`