SOLVED

New Contributor

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

5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Converting Min to Decimal with a long string

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

# Re: Converting Min to Decimal with a long string

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.

# Re: Converting Min to Decimal with a long string

@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?

# Re: Converting Min to Decimal with a long string

I should have mentioned thanks for the help with this. Yes, we input the numbers and can change the cell format to whatever works.

# Re: Converting Min to Decimal with a long string

I got it to work by changing the cell format to Text. Thanks a bunch for the help; you're awesome.