Time conversion Question

[crope1]

I have a report to reports a duration in the following format. It is days/Hours/minutes/seconds as applicable. Is there a way to write a formula to convert these to seconds? Thanks!

17m 6s 1d 22h 56m 12s

 

[Toadstool]

Hi Crope1

Does this work for you?

Crope1.xlsx
	A	B
1	Time	Time in Seconds
2	17m 6s	1026
3	1d 22h 56m	168960
4	12s	12
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=(IFERROR(LEFT(MID(" "&A2,SEARCH("d"," "&A2)-2,2),2)*86400,0))+(IFERROR(LEFT(MID(" "&A2,SEARCH("h"," "&A2)-2,2),2)*3600,0))+(IFERROR(LEFT(MID(" "&A2,SEARCH("m"," "&A2)-2,2),2)*60,0))+(IFERROR(LEFT(MID(" "&A2,SEARCH("s"," "&A2)-2,2),2),0))

 

[crope1]

Hi Crope1

Does this work for you?

Crope1.xlsx
	A	B
1	Time	Time in Seconds
2	17m 6s	1026
3	1d 22h 56m	168960
4	12s	12
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=(IFERROR(LEFT(MID(" "&A2,SEARCH("d"," "&A2)-2,2),2)*86400,0))+(IFERROR(LEFT(MID(" "&A2,SEARCH("h"," "&A2)-2,2),2)*3600,0))+(IFERROR(LEFT(MID(" "&A2,SEARCH("m"," "&A2)-2,2),2)*60,0))+(IFERROR(LEFT(MID(" "&A2,SEARCH("s"," "&A2)-2,2),2),0))

I believe so. If the data is in a different field, say G2, would I just need to change all A2 references in the formula to the corresponding data cell?

 

[Toadstool]

Yes, like this

Crope1.xlsx
	G	H
1	Time	Time in Seconds
2	17m 6s	1026
3	1d 22h 56m	168960
4	12s	12
ColG

Cell Formulas
Range	Formula
H2:H4	H2	=(IFERROR(LEFT(MID(" "&G2,SEARCH("d"," "&G2)-2,2),2)*86400,0))+(IFERROR(LEFT(MID(" "&G2,SEARCH("h"," "&G2)-2,2),2)*3600,0))+(IFERROR(LEFT(MID(" "&G2,SEARCH("m"," "&G2)-2,2),2)*60,0))+(IFERROR(LEFT(MID(" "&G2,SEARCH("s"," "&G2)-2,2),2),0))

 

[crope1]

Awesome! Thanks!