SOLVED

XL

%3CLINGO-SUB%20id%3D%22lingo-sub-615719%22%20slang%3D%22en-US%22%3EXL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615719%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20recently%20imported%20my%20Flight%20Logbook%20with%2030%20years%20of%20aeroplane%20flight%20data%20into%20an%20XL%20file.%3C%2FP%3E%3CP%3EHow%20do%20I%20calculate%20the%20total%20hours%20and%20minutes%20of%20flying%20totals%20when%20the%20flight%20goes%20through%20midnight%3F%3C%2FP%3E%3CP%3Ei.e.%20through%2024%3A00%20hours...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-615719%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-615764%22%20slang%3D%22en-US%22%3ERE%3A%20XL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615764%22%20slang%3D%22en-US%22%3E%3CP%3EAssuming%20flights%20do%20not%20exceed%2024hrs%20then%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20MOD(%20EndTime%20-%20StartTime%2C%201%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eshould%20do%20it.%20More%20generally%20you%20would%20need%20to%20use%20the%20date%2Ftime%20in%20which%20the%20integer%20part%20represents%20the%20date%20and%20the%20fractional%20part%20is%20the%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-622249%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20XL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-622249%22%20slang%3D%22en-US%22%3EHi%20Peter%3CBR%20%2F%3EThanks%20for%20your%20reply.%20I%20wrote%20the%20formula%20that%20you%20suggested%20but%20it%20simply%20calculated%2000.00%20when%20applied...%20any%20ideas%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-622253%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20XL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-622253%22%20slang%3D%22en-US%22%3EScrub%20my%20last%20message.%20It%20was%20just%20my%20ineptitude.%20It%20works!%20Thanks%20%F0%9F%99%8F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-622264%22%20slang%3D%22en-US%22%3ERe%3A%20XL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-622264%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3D(EndTime%2B(EndTime%3CSTARTTIME%3E%3C%2FSTARTTIME%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-622371%22%20slang%3D%22en-US%22%3ERe%3A%20XL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-622371%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%20Peters%20formula%20works%20-%2099.9%25%20of%20my%20hours%20are%20rounded%20to%20the%20nearest%205%20mins%20but%20there%20is%20a%20cell%20that%20isn't%20somewhere%20in%20the%20totals%20column%20-%20how%20do%20I%20spot%20it%2Fthem%20to%20round%20them%20please%3F%20Already%20tried%20the%20search%20function%20for%208%20in%20a%20highlighted%20column...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-625694%22%20slang%3D%22en-US%22%3ERe%3A%20XL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-625694%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345051%22%20target%3D%22_blank%22%3E%40BEN_FAWKES%3C%2FA%3E%26nbsp%3B%2C%20in%20general%20rounding%20to%20nearest%205%20min%20is%20as%3C%2FP%3E%0A%3CPRE%3E%3DMROUND(A1%2C%220%3A05%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
BEN_FAWKES
Occasional 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 🙏
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...

Solution

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

=MROUND(A1,"0:05")
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies