Convert seconds column into duration column hh:mm:ss.ss (needs hundredths of a second in output)

[Kenny_McMillan]

I am trying to convert a column that contains a time in seconds into a duration that includes hundreds of a second

eg

Convert 242.5 secs into  00:04:02.05

Regards

[v-jiascu-msft]

Hi @Kenny_McMillan,

Seems there isn't a built-in support of such format. Please vote up this idea here. The workaround could be as below.

Column 2 =
VAR hours =
    ROUNDDOWN ( [Column1] / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [Column1], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [Column1], 60 ) )
VAR milliseconds =
    ROUND ( MOD ( [Column1], 1 ) * 100, 0 )
RETURN
    hours & ":"
        & minutes
        & ":"
        & seconds
        & "."
        & milliseconds

Best Regards,

Dale

[Kenny_McMillan]

Thanks Dale for the help, 

How would you force each number to be of two digits length ?  4 minutes 2.17 secs would be 00:04:02.17

Regards

[v-jiascu-msft · Accepted solution]

Hi @Kenny_McMillan,

Try out this formula please.

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

Best Regards,

Dale

[Kenny_McMillan]

Thank you so much Dale this is great.  I assume there is no way that this can be forced into a Duration format though ?

[v-jiascu-msft]

Hi @Kenny_McMillan,

There is a Duration type for the Power Query while there isn't a Duration type for DAX. That's why I suggested you vote up the idea.

Best Regards,

Dale

[Kenny_McMillan]

Thank you dale