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

 

[XOR LX]

Hi,

So you mean that your example would actually be in the form 05d 06h 03m 55s?

Regards

 

[Rick Rothstein]

So you mean that your example would actually be in the form 05d 06h 03m 55s?

To follow up on XOR LX's question, is each part shown even if that part's value is 0? For example, if the hours and seconds were 0, would it show up as this...

05d 0h 03m 00s

or this..

05d 03m

 

[Davej31]

No sorry. If there is only one digit it will show only one digit
eg
[TABLE="width: 121"]
<tbody>[TR]
[TD]0d 7h 2m 22s[/TD]
[/TR]
[TR]
[TD]0d 0h 4m 29s[/TD]
[/TR]
[TR]
[TD]0d 0h 3m 15s[/TD]
[/TR]
[TR]
[TD]0d 1h 2m 24s[/TD]
[/TR]
[TR]
[TD]0d 0h 0m 21s[/TD]
[/TR]
[TR]
[TD]0d 1h 25m 18s[/TD]
[/TR]
[TR]
[TD]0d 1h 5m 31s

Thanks

D[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Davej31]

Hi

Each part is shown

Thanks

David

 

[Rick Rothstein]

This formula should work...

=86400*LEFT(A1,FIND("d",A1)-1)+3600*MID(A1,FIND("d",A1)+1,FIND("h",A1)-FIND("d",A1)-1)+60*MID(A1,FIND("h",A1)+1,FIND("m",A1)-FIND("h",A1)-1)+MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1)

 

[XOR LX]

Hopefully also this:

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

Regards

 

[Davej31]

Rick

many thanks

Works like a dream

Regards

David

 

[Rick Rothstein]

Rick

many thanks

Works like a dream

Use XOR LX's formula (Mssage #7)... it is much better than the one I posted.

 

[XOR LX]

Use XOR LX's formula (Mssage #7)... it is much better than the one I posted.

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("000"&SUBSTITUTE(A1," ",REPT(" ",3)),FIND({"d","h","m","s"},"000"&SUBSTITUTE(A1," ",REPT(" ",3)))-4,4),{86400,3600,60,1})

which will now account for up to 9999 days.

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

Regards