HELP !! Convert 1d 00h 52m to h:mm:ss

[Thuisgevoel]

MS Excel 2002 sp3 on an winxpsp2 system

Ive been looking around in the forums in the hope to find a solution that would specifically help me solve my problem of converting this unuseable time issue to a useable one but sadly enough i havent found an understandable solution so im posting a request for help.

I am given in an excel 95 sheet (from an external database) data with time displayed in the format 1d 00h 52m (cell c3) with property values shown as [h]:mm:ss and would like to convert the visual value d:h:m to a visual value hh:mm:ss therefore saying that 1d 00h 52m wil become 24:52:00

Who can assist me! Anyone?

 

[Andrew Poulsom]

Try:

=LEFT(C3,FIND("d",C3)-1)+SUBSTITUTE(SUBSTITUTE(RIGHT(C3,LEN(C3)-FIND(" ",C3)),"m",""),"h ",":")

formatted as [hh]:mm:ss

 

[Thuisgevoel]

Andrew,

Thank you for your tip. I inserted the code after the language conversion and that seems to work. Except for the fact that what i was trying to reach was not written in my question. Regardless of that fact my thanks.