SOLVED

Converting Min to Decimal with a long string

Copper Contributor

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

@ToddLock 

According to the Excel specs:

Riny_van_Eekelen_0-1661496987457.png

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.
I got it to work by changing the cell format to Text. Thanks a bunch for the help; you're awesome.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@ToddLock 

According to the Excel specs:

Riny_van_Eekelen_0-1661496987457.png

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

 

View solution in original post