Forum Discussion

AparnaJ's avatar
AparnaJ
Helper I
7 years ago
Solved

Convert text to time

Hi,

 

Have a file as below:

 

Seconds

300

814000

 

Am trying to create a new column using DAX as below:

-------------------------------------------------------------------------------------------------------------------------
Time Format =
Var H = INT(Sheet1[Seconds]/3600)
Var M = FORMAT(INT(DIVIDE(MOD(Sheet1[Seconds],3600),60)), "00")
Var S = FORMAT(INT(MOD(MOD([Seconds],3600),60)),"00")
RETURN
H& ":" & M & ":" & S
--------------------------------------------------------------------------------------------------------------------------------
However, am getting an error when I change the data type of the new column to Time as:
"Cannot convert value '226:06:40' of type text to type date.
Please advice.
 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi AparnaJ 

    Create columns

    Time Format = 
Var H = MOD(INT(Sheet1[Seconds]/3600),24)
Var M = FORMAT(INT(DIVIDE(MOD(Sheet1[Seconds],3600),60)), "00")
Var S = FORMAT(INT(MOD(MOD([Seconds],3600),60)),"00")
RETURN
H& ":" & M & ":" & S

day = INT(INT(Sheet1[Seconds]/3600)/24)

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    AparnaJ  - 

    That only works up to 23:23:59.

    Does it need to be type time instead of the formatted time?

    Cheers,

    Nathan

    • AparnaJ's avatar
      AparnaJ
      Helper I
      7 years ago

      Thank you. Is there a way that we can change to type time. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Type Time is referring to a clock time.  https://dax.guide/time/

        That is why, as mentioned by natelpeterson, the maximum value is 23:59:59.  Because a clock only has 24 hours.


        Type Time is not meant to be used for a duration of time.  To display it in the format you're asking for with DAX, the only format you could use would be text.

        Maybe clarify your requirements as to why it needs to be displayed in the specific format of 00:00:00.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi AparnaJ 

    Create columns

    Time Format = 
Var H = MOD(INT(Sheet1[Seconds]/3600),24)
Var M = FORMAT(INT(DIVIDE(MOD(Sheet1[Seconds],3600),60)), "00")
Var S = FORMAT(INT(MOD(MOD([Seconds],3600),60)),"00")
RETURN
H& ":" & M & ":" & S

day = INT(INT(Sheet1[Seconds]/3600)/24)

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Tani4ka's avatar
      Tani4ka
      Helper II
      5 years ago

      This doesn't seem to work in my case since I have cells with no values. How to get along this one?

      Many thanks