Aug 25 2022 11:44 PM
I'm looking for an excel formula that will convert the Minutes to Decimal for aircraft time which usually has five or six digits in front of the Colon: for example, 12999:30, the calculation would be 12999.50.
When I tried to use the standard formulas like =A1*24 or =(A1/24)*576 it only worked if there are four or fewer digits in front of the colon. Example 1234:30 works 12345:30 doesn't work #VALUE! error.
Any help would be greatly appreciated.
Aug 25 2022 11:58 PM - edited Aug 26 2022 12:00 AM
SolutionAccording to the Excel specs:
So I guess you'll have to use a formula like:
=LEFT(A1,FIND(":",A1)-1)+RIGHT(A1,2)/60
Assuming that the minutes will always be the last two digits (thus 00 to 59).
Aug 26 2022 12:10 AM
Aug 26 2022 12:23 AM
@ToddLock I forgot to mention that the hours:minutes should be texts. I noticed that the formula works on hours:minutes that exceed the maximum as Excel automatically sees it as a text. Entering a smaller figure, and Excel translates it to a date/time value. Then the formula doesn't work as it doesn't find the semi-colon.
Where do the time entries come from? Import or do you enter them yourself?
Aug 26 2022 12:28 AM
Aug 26 2022 12:36 AM
Aug 25 2022 11:58 PM - edited Aug 26 2022 12:00 AM
SolutionAccording to the Excel specs:
So I guess you'll have to use a formula like:
=LEFT(A1,FIND(":",A1)-1)+RIGHT(A1,2)/60
Assuming that the minutes will always be the last two digits (thus 00 to 59).