Forum Discussion
Solved
Converting Min to Decimal with a long string
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.
According to the Excel specs:
So I guess you'll have to use a formula like:
=LEFT(A1,FIND(":",A1)-1)+RIGHT(A1,2)/60Assuming that the minutes will always be the last two digits (thus 00 to 59).
According to the Excel specs:
So I guess you'll have to use a formula like:
=LEFT(A1,FIND(":",A1)-1)+RIGHT(A1,2)/60Assuming that the minutes will always be the last two digits (thus 00 to 59).
- Your assumption is correct, this is for aircraft time which will always be between 00 to 59. I have tried the formula, and doesn't seem to work, maybe something I'm doing wrong.
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?
