HH:MM:SS to decimal 1

[ptrifile]

How can i convert 0:4:30 to 4.5 or 0:3:50 to 3.83?

Thanks for any help!

Paul

 

[combo]

Multiply input by 24 and format as number

combo

 

[SkipVought]

Hi,

Are you ABSOLUTELY SURE that you have a STRING structure as 0:4:30 ?

What happens if your FORMAT this cell as GENERAL? If it 'chnages' to a number, you already have your answer.

Otherwise you must 1) parse the HRS, MINS, SECS and convert MINS & SECS to Hrs, a third grade exersize.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptrifile]

that does not give the desired results. For example if i use your suggestion on 0:7:15 the result is 0.12 when i would expect it to be 7.25.

Am i doing something incorrectly?

 

[SkipVought]

You want to convert to MINUTES.

So PARSE & then convert HRS to Minutes and SECS to Minutes and sum.

Again, a third grade conversion.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Multiply by 1440 (=24*60). In excel 1h equals formatted 1/24, I missed that you calculate minutes.

combo

 

[ptrifile]

Skip, if I take the cell with 0:7:15 and format it as general i get 0.005034722.....At one time i found a formula that would take 0:7:15 and convert that to 7.25....Unfortunately i do not remember where i found it.

 

[ptrifile]

Combo, thank you! that worked perfectly!

 

[combo]

BTW, 0:7:15 can be a result of h:m:ss format.

combo

 

[SkipVought]

format it as general i get 0.005034722

That is DAYS: hence the conversion that combo posted from DAYS to MINUTES: again a third grade exersize.

faq68-5827.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

You already have the answer, this is just for reference

You can also do
=HOUR(B2)+MINUTE(B2)/60+SECOND(B2)/60 (format the cell as General)

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.