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...
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)/60
Assuming 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?
- I should have mentioned thanks for the help with this. Yes, we input the numbers and can change the cell format to whatever works.
