How to convert seconds to hh:mm:ss

[JohannB]

I have a column which is integer data type and it represents number of seconds. 

When i use it in a matrix it gives me a figure eg: 499

I need to display this as 00:08:19

Should I create a new measure? If I do it keeps complaining about the syntax

Can you help please

[Anonymous]

I used this formula to get this to work: 

AHT =

VAR TotaalSeconden=AVERAGE('Sheet'[Duration seconden HT])

VAR Uren = TRUNC((TotaalSeconden)/3600)

VAR Minuten =TRUNC(MOD(TotaalSeconden,3600)/60)

VAR Seconden = MOD(TotaalSeconden,60)

return FORMAT(TIME(Uren,Minuten,Seconden),"hh:mm:ss")

Duration seconden HT is the added column that shows the time in between timestamps in seconds., using: 

Duration seconden HT =

HOUR('Sheet'[Totale Afhandeltijd])*3600+MINUTE('Sheet'[Totale Afhandeltijd])*60+SECOND('Sheet'[Totale Afhandeltijd])

[sdjensen]

Hi, 

does anyone know of a similar formula to convert days into years, months and days?

[Greg_Deckler]

I have an article on that:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Also, I have a Quick Measure for that in the Gallery for the reverse.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279

[JohannB]

I have used your DAX formula to create a new measure in my dataset but it returns a syntax error.

Duration =
// Duration formatting
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [TalkTime]
// There are 3,600 seconds in an hour
VAR Hours =
INT ( TalkTime/ 3600)
// There are 60 seconds in a minute
VAR Minutes =
INT ( MOD( TalkTime - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
ROUNDUP(MOD ( MOD( TalkTime - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
CONCATENATE (
H,
CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
)

Please assist

[Greg_Deckler]

What's the error?

[JohannB]

This is what is displayed on the screen.

TalkTime is the measure I want to change to hh:mm:ss

Thank you in advance

[JohannB]

I have used the dax formula to convert seconds to hh:mm:ss successfully.

When used in a matrix it either gives me the earliest or latest option as displayed in the attached example.

What can I do to display the summarised seconds in hh:mm:ss.

Currently the converted dax data is in text form.

Thank you in advance

[Greg_Deckler · Accepted solution]

So, you have to aggregate first when the duration is in seconds or minutes or hours and then convert it to hh:mm:ss

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486