Convert An Int Field to Time Format

[Anonymous]

Hi,

I want to convert an INT feild(Time Spent On Calls In secs ) in a matrix table to hh:mm:ss format ,how to do that ?

[VasTg · Accepted solution]

@Anonymous 

Refer to these posts.

https://community.powerbi.com/t5/Desktop/Convert-seconds-column-into-duration-column-hh-mm-ss-ss-needs/td-p/371469

https://community.powerbi.com/t5/Desktop/convert-seconds-column-into-duration-column-mm-ss/td-p/631155

https://community.powerbi.com/t5/Desktop/Seconds-to-HH-MM-SS/td-p/348817

If it helps, mark it as a solution

Kudos are nice too

[dax · Accepted solution]

Hi @Anonymous , 

If you want to convert second to hh:mm:ss, I think you could refer to above links to convert it. If you are using measure, you also could try below to achieve this goal

measure = 
VAR hours =
    ROUNDDOWN ( [measure] / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [measure], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [measure], 60 ) )
VAR milliseconds =
    round(MOD ( [measure], 1 ) * 100,0)
RETURN
    FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")
        & "."
        & FORMAT(milliseconds, "00")

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

if you need a solution in Power Query, this concept would work out

let
    Source = #table({"Time_Spent_On_Calls"}, {{"15292"}, {"16163"}}),
    ChangedType = Table.TransformColumnTypes(Source,{{"Time_Spent_On_Calls", Int64.Type}}),
    AddDays = Table.AddColumn(ChangedType, "GetDays", each [Time_Spent_On_Calls]/60/60/24, type number ),
    AddedDuration = Table.AddColumn(AddDays, "Duration", each Duration.From([GetDays])),
    AddedHHMMSS = Table.AddColumn(AddedDuration, "hh:mm:ss", each Text.From(Number.RoundDown (Duration.TotalHours([Custom])))&":"&Text.From(Duration.Minutes([Custom]))&":"&Text.From(Duration.Seconds([Custom])))
in
    AddedHHMMSS

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[VasTg · Accepted solution]

@Anonymous 

Refer to these posts.

https://community.powerbi.com/t5/Desktop/Convert-seconds-column-into-duration-column-hh-mm-ss-ss-needs/td-p/371469

https://community.powerbi.com/t5/Desktop/convert-seconds-column-into-duration-column-mm-ss/td-p/631155

https://community.powerbi.com/t5/Desktop/Seconds-to-HH-MM-SS/td-p/348817

If it helps, mark it as a solution

Kudos are nice too

[Anonymous]

 my problem is i have duration in seconds column in a table and i am doing the sum of the column per user name and showing it in the matrix table , but my client wants this to show it as hh:mm:ss format , which i am not able to do it 

[dax · Accepted solution]

Hi @Anonymous , 

If you want to convert second to hh:mm:ss, I think you could refer to above links to convert it. If you are using measure, you also could try below to achieve this goal

measure = 
VAR hours =
    ROUNDDOWN ( [measure] / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [measure], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [measure], 60 ) )
VAR milliseconds =
    round(MOD ( [measure], 1 ) * 100,0)
RETURN
    FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")
        & "."
        & FORMAT(milliseconds, "00")

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.