Convert text to time

[AparnaJ]

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.

[v-juanli-msft · Accepted solution]

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]

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

Many thanks

[Anonymous]

@AparnaJ  - 

That only works up to 23:23:59.

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

Cheers,

Nathan

[AparnaJ]

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

[Anonymous]

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.