Articles

Solved Convert number to time to decimal time in Excel

November 14, 2012 at 16:42:27
Specs: Windows 7

I have an Excel cell (general format) with the number 7.40 in it. It's supposed to mean 7 hours, 40 minutes. I need to multiply what 7.40 really means (7.67 hours) by a rate for another cell total. How do I convert the 7.40 to 7.67? (I realize I could just change the 7.40 to 7.67, but I'm being simplistic - there are thousands of cells with the data entered this awful hh.mm way.)

See More: Convert number to time to decimal time in Excel

Report •


#1
November 14, 2012 at 17:37:25

The trailing zeros are the main problem, as Excel will drop them, so 7.40 becomes 7.4

Here is a work around.

With your numbers in column A
in column B enter the formula: =TEXT(A1,"00.00")
which will convert your number to text and retain any trailing zeros

Your column B should look like: 07.40

Next, in column C enter the formula:

=TIME(LEFT(B1,2),RIGHT(B1,LEN(B1)-FIND(".",B1)),)*24

Which will now convert your text string back into a time and
by multiplying by 24 get your decimal number.

See how that works.

MIKE

http://www.skeptic.com/


Report •

#2
November 14, 2012 at 17:56:05
✔ Best Answer

Here is an updated version that does not require the helper column.

With your numbers in column A
enter this formula in Column B

=TIME(LEFT(TEXT(A1,"00.00"),2),RIGHT(TEXT(A1,"00.00"),2),)*24

MIKE

http://www.skeptic.com/


Report •

#3
November 14, 2012 at 18:11:09

Worked like a charm! Thanks so much!

Report •

Related Solutions


Ask Question