Convert hh:mm:ss to decimal time

[vickersja]

Hello,

I am having problems converting hours, minutes, and seconds to decimal time.

Example:

I have 08:32:24 in cell A1.

I use this formula in B1: =(A1-INT(A1))*24

But I get the #VALUE! error. I tried chaning the format of the cell, but that didnt work.

I know there is a way to manually calculate out the minutes and seconds, but I need to do this for many differnet fields with different values, so I am looking for a quick formuala.

Any help would be appreciated. Thank you.

 

[jbeaucaire]

Your formula is sound, works for me whether A1 is entered as a time value or a text value.

Excel Workbook
	A	B	C
1	08:32:24	8.54	TEXT
2	8:32:24	8.54	TIME
Sheet3

 

[barry houdini]

You may have some other characters in the cell, try this formula to extract just the time and multiply by 24

=LEFT(A1,FIND(":",A1)+5)*24

format result cell as number

 

[vickersja]

Thank you. Here is the odd thing. When I use the exact same formula in excel, I am still getting the Value error.

But when I copy and paste from you post into excel, it works fine. Im not sure why...

 

[wsjackman]

Hello,

I am having problems converting hours, minutes, and seconds to decimal time.

Example:

I have 08:32:24 in cell A1.

I use this formula in B1: =(A1-INT(A1))*24

But I get the #VALUE! error. I tried chaning the format of the cell, but that didnt work.

I know there is a way to manually calculate out the minutes and seconds, but I need to do this for many differnet fields with different values, so I am looking for a quick formuala.

Any help would be appreciated. Thank you.

I could get a value error if there was a space in front of the time
try

=(trim(A1)-INT(trim(A1)))*24

 

[vickersja]

That did it! Thank you all very much.

 

[mcintoshmc]

Your formula is sound, works for me whether A1 is entered as a time value or a text value.

Excel Workbook
	A	B	C
1	08:32:24	8.54	TEXT
2	8:32:24	8.54	TIME
Sheet3

Hi, I wanted to pick this up again. The formula works, but how would I do it if it were to look something like this? 168:08:00

 

[Rick Rothstein]

Hi, I wanted to pick this up again. The formula works, but how would I do it if it were to look something like this? 168:08:00

Since you are only entering a time value, this seems to work...

=24*TRIM(A1)

but after entering the formula, you have to format the cell as General.