SOLVED

Changing data to hours

Copper Contributor

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?

5 Replies

 

DayHoursHoursHours
116"=O17*24+P1716

 

Hope answer your question

@astokes8 

Here it is. You may change the cell reference A1 accordingly.
=(LEFT(A1,SEARCH("d",A1)-1)+TIMEVALUE(SUBSTITUTE(RIGHT(LOWER(A1),LEN(A1)-SEARCH(" ",A1)),"h",":0")))*24&"h"
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”
best response confirmed by Hans Vogelaar (MVP)
Solution

@astokes8@astokes8

It works.

Starrysky1988_0-1648060107102.png

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

 

 

Thanks so much!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@astokes8@astokes8

It works.

Starrysky1988_0-1648060107102.png

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

 

 

View solution in original post