Converting 5d 6h 3m 55s into seconds

[Davej31]

Hi All

I know there are some formulas out there that can do this but I am having difficulty in getting a one to convert the above format to seconds.

Any assistance would be appreciated

the format is xxd xxh xxm xxs

Thanks

D

 

[Rick Rothstein]

Thanks, though actually it would fail if the days portiion were greater than 99, so unfortunately it needs to be lengthened slightly:

=SUMPRODUCT(0+MID("00"&SUBSTITUTE(A1," "," "),FIND({"d","h","m","s"},"00"&SUBSTITUTE(A1," "," "))-3,3),{86400,3600,60,1})

which will now account for up to 999 days.

It can easily be made to account for more, though that should be sufficient, no?

I saw that, but since the OP didn't say that could be the case, I went with "it won't be the case" figuring the OP would come back if there were that many days possible. Besides, you fix does not work for single digits (no leading zero)... the -3 reaches too far back. For example...

1d 2h 3m 4s

 

[XOR LX]

I saw that, but since the OP didn't say that could be the case, I went with "it won't be the case" figuring the OP would come back if there were that many days possible. Besides, you fix does not work for single digits (no leading zero)... the -3 reaches too far back. For example...

1d 2h 3m 4s

Actually the editor replaced by "[double-space]" with "[single-space]" for the 3rd argument of the SUBSTITUTE in that version of the formula!!

So I've changed it to include a (rather unnecessary) REPT function!

Regards

 

[Rick Rothstein]

I saw that, but since the OP didn't say that could be the case, I went with "it won't be the case" figuring the OP would come back if there were that many days possible. Besides, you fix does not work for single digits (no leading zero)... the -3 reaches too far back. For example...

1d 2h 3m 4s

Actually, your formula would have worked if it had not gotten changed by this forum's comment processor... the double spaces got collapsed down to single spaces... here is your formula with the double spaces intact.

=SUMPRODUCT(0+MID("00"&SUBSTITUTE(A1," ","  "),FIND({"d","h","m","s"},"00"&SUBSTITUTE(A1," ","  "))-3,3),{86400,3600,60,1})

 

[XOR LX]

Indeed! Looks like we "crossed" on that last exchange.

And thanks for posting the corrected version.

Regards