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

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 zerosYour 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

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

Worked like a charm! Thanks so much!

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History