transform text to a DD:HH:MM:SS format

**akasha303** · 10-19-2011, 08:23 AM

Hi

Looking for assistance writing a formula to transform the below entry to a DD:HH:MM:SS format.

8d 15h 18m 54s

I've tried manually manipulating using 'Find replace', and get the data to 8:15:18:54 ... however, this isn't recognised as a valid date format ...

Any ideas welcomed

Thanks
Greg

**NBVC** · 10-19-2011, 08:38 AM

A few nested SUBSTITUTE functions...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1," ",":"),"d",""),"h",""),"m",""),"s","")

where C1 contains text

**TMS** · 10-19-2011, 08:40 AM

You can format a time as dd:hh:mm:ss or as [hh]:mm:ss but first you'd have to convert the values from the text into a number representing a time.

All you've done with the Find/Replace is convert one text value to another text value.

A Time is a numeric value representing a part of a day. One (1) day is 24 hours, 15 hours is 15/24 of a day, 18 minutes is 18/(24*60) of a day.

Extract the values using MID, multiply by their respective factors, add them up and format as required.

Regards

**akasha303** · 10-19-2011, 09:24 AM

Thanks both, appreciate your input

**NBVC** · 10-19-2011, 09:31 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save