Forum Discussion

Copper Contributor

Mar 23, 2022

Solved

Changing data to hours

I have data that has been entered in days and hours; i.e. 1d 16h. Is there a way to use a formula to change this data to hours; i.e. 40h?

excel

Starrysky1988
Mar 23, 2022
astokes8 astokes8
It works.
=(LEFT(A1,SEARCH("d",A1)-1)+TIMEVALUE(SUBSTITUTE(RIGHT(LOWER(A1),LEN(A1)-SEARCH(" ",A1)),"h",":0")))*24&"h"

astokes8

Copper Contributor

Mar 23, 2022

Thanks. I just tried that formula with one cel and it’s just showing #VALUE! Is there something else I need to do? The cell I tried it on reads “2d 1h”

Starrysky1988

Iron Contributor

Mar 23, 2022

astokes8 astokes8

It works.

=(LEFT(A1,SEARCH("d",A1)-1)+TIMEVALUE(SUBSTITUTE(RIGHT(LOWER(A1),LEN(A1)-SEARCH(" ",A1)),"h",":0")))*24&"h"

astokes8
Copper Contributor
Mar 25, 2022
Thanks so much!