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.
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
Worked like a charm! Thanks so much!
