How to change the values to Minutes/ Hours

Not sure if we have all of the details, but

With
A1 containing date/time text in the format you posted.....0d 2h 18m 27s

This formula converts that value into an Excel date/time, which you can format any way you like

B1: =--(LEFT(A1,SEARCH("d",A1)-1)*24
+TRIM(RIGHT(LEFT(A1,SEARCH("h",A1)-1),2))
&":"&TRIM(RIGHT(LEFT(A1,SEARCH("m",A1)-1),2))
&":"&TRIM(LEFT(RIGHT(A1,3),2)))

In the above example, that formula returns: 0.0961458333333333

When custom number formatted as [m]:ss
the result displays as: 138:27

Is that something you can work with?

Hi Ron... This is just brilliant... However, there's a little issue here..

No of Days can go up to 31 days.. whereas in my example it is 0 days and it seems that the formula is working upto only single digit dates


Hours/ minutes and Seconds can also be upto 2 digits.. so the formula needs to take care of double digits as well as single digits..

Any solution possible

For example.. the formula is giving an incorrect value for "20d 17h 20m 34s".. The formula result is '5200060' where as the actual value should be '102460'.

I'm not seeing that.

For this input: 20d 17h 20m 34s

The text part of the formula I posted resolves to this: "497:20:34"

Converted to a number, "497:20:34" becomes: 20.7226157407407

When that value has a custom number format of [m]:ss
the result is: 29840:34

20*24+17 = 497 hours


497 hours = 29820 minutes
+20 min = 29840
+34 sec = 29840:34 (which is what the formula I posted returns)

What am I missing?

After finding the position of a character (like "d") in the string, we don't want to include it in the numeric portion.
Example:
to extract the number of days from 20d
LEFT(A1,SEARCH("D",A1)) would return 20d
but, this...LEFT(A1,SEARCH("D",A1)-1) returns 20

The "&" is used for string concatenation. It makes the CONCATENATE function unnecessary.

This: CONCATENATE("A","B","C")
is the same as this: "A"&"B"&"C".

Both approaches return: "ABC"