Convert Text to Duration

[brettdowden]

Hello,

I have a column of duration data formatted as text. It's in the format, for example, 2d 12h 15m. If it's less than a day, it won't have the d field. I'd like to convert this text to a duration or date that I can then use to make averages, etc. Any help would be appreciated.

Thanks,
Brett

[tigeravatar]

brettdowden,

Welcome to the forum!
You can use this formula to convert the text into a usable date/time number:
=IF(COUNTIF(A1,"*d*"),LEFT(A1,SEARCH("d",A1)-1),0)+MID(A1,SEARCH("h",A1)-2,2)/24+MID(A1,SEARCH("m",A1)-2,2)/1440

The results will be the number of days, and then a fraction of a day representing the hours and minutes. Format the cell containing the result as custom with:
[h]:mm;@

Using the example you provided, 2d 12h 15m, the result will be:
60:15 (60 hours, 15 minutes)

[brettdowden]

Thank you so much. I just had to make a slight variation for the cases where there was no "d" portion AND there was only 1 hour digit. Once you gave me the starting point, I could figure it out.

Brett