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.


Quote Originally Posted by shg View Post
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

      -----A----- ---B----
  1      Input     Output 
  2   55M 25S     00:55:25
  3   01H 32S     01:00:32
  4   01H 01M 15S 01:01:15
  5   01H 03M 11S 01:03:11
  6   01H 04M 32S 01:04:32