Excel 2007 : Convert time

[pappu07]

Hi,

I have an excel sheet with the below entries:

55M 25S
01H 32S
01H 01M 15S
01H 03M 11S
01H 04M 32S

and I need to convert it into decimal value:

55:25
01:00:32
01:01:15
01:03:11
01:04:32

Please help me with this.

[Marcol]

One way assuming your data is in A2 down
In B2

Please Login or Register  to view this content.

Drag/Fill Down

Format Column B Number Format > Custom Type:= hh:mm:ss

[shg]

With a string like that in A2, select B2 and do Insert > Name >Define

ToTime Refers to: =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "H ", "/24 + "),"M ", "/1440 + "), "S", "/86400"))

Then in B2 and copy down, =ToTime

Please Login or Register  to view this content.

[daddylonglegs]

You could use this formula

=SUM(MID(A2&"000",FIND({"H","M","S"},A2&"xxHMS")-2,2)/{1,60,3600})/24

[shg]

That's very clever, dll.

[Marcol]

Interesting.

shgs' solution requires to be saved as a macro-enabled workbook 2007 and later.
The function EVALUATE(), an Excel 4.0 function is treated as a macro.

DLLs' formula can be saved as a macro free workbook.

[EDIT]
Don't mess with the Time-Lord ... !!!

[pappu07]

It worked great, thanks for your help.

I just got one more spreadsheet with the below entries..

1day 09H 52M 33S
2day 06H 31M 28S
1day 08H 17M 46S

and needs to be converted in the same way. But, we need to convert the 1 day into hours.

I would greatly appreciate if you could help me with this.

With a string like that in A2, select B2 and do Insert > Name >Define

ToTime Refers to: =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "H ", "/24 + "),"M ", "/1440 + "), "S", "/86400"))

Then in B2 and copy down, =ToTime

Please Login or Register  to view this content.

[pappu07]

It worked great, thanks for your help.

I just got one more spreadsheet with the below entries..

1day 09H 52M 33S
2day 06H 31M 28S
1day 08H 17M 46S

and needs to be converted in the same way. But, we need to convert the 1 day into hours.

I would greatly appreciate if you could help me with this.

One way assuming your data is in A2 down
In B2

Please Login or Register  to view this content.

Drag/Fill Down

Format Column B Number Format > Custom Type:= hh:mm:ss

[daddylonglegs]

This formula should work for your previous examples and also the ones with days (up to 99 days)

=SUM(MID(0&A1&"0000",FIND({"d","H","M","S"},A1&"xxdHMS")-1,2)/{1,24,1440,86400})

custom format result cell as [hh]:mm:ss - note square brackets - required to show hours totals of 24 or over

[pappu07]

Awesome...thanks much.