Forum Discussion

astokes8's avatar
astokes8
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

5 Replies

  • Starrysky1988's avatar
    Starrysky1988
    Iron Contributor
    Mar 23, 2022
    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"
    • astokes8's avatar
      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's avatar
        Starrysky1988
        Iron Contributor
        Mar 23, 2022

        astokes8astokes8

        It works.

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

         

         

Resources