Seconds 2 HHMMSS 06-16-2017 04:37 AM

[Wouter]

How many hours ins 532813 seconds.....

# Time spent HH:MM:SS =

// INPUT seconds ==>  RETURNS "hh:mm:ss"

// 1) What's the input => Which measure should we work with?

VAR Input = SUM(TimeSpint[Seconds Spent])

// 2) Extract the number of hours

VAR Hours         = INT ( Input / 3600)

// 3) Extract the remaining minutes

VAR Minutes = INT ( MOD( Input - ( Hours * 3600 );3600 ) / 60)

// 4) Extract the remaining seconds

VAR Seconds = ROUNDUP(MOD ( MOD( Input - ( Hours * 3600 );3600 ); 60 );0)

// 5) Format       Hours    ==> HH,           Minutes ==> MM,Seconds ==> SS

VAR HH = IF ( LEN ( Hours )     = 1;CONCATENATE ( "0"; Hours       );CONCATENATE ( ""; Hours        ))

VAR MM = IF ( LEN ( Minutes ) = 1;CONCATENATE ( "0"; Minutes    );CONCATENATE ( ""; Minutes     ))

VAR SS = IF ( LEN ( Seconds )   = 1;CONCATENATE ( "0"; Seconds    );CONCATENATE ( ""; Seconds ))

// 6) Format == Concat to HH:MM:SS

RETURN

IF(         CONCATENATE (HH;CONCATENATE ( ":"; CONCATENATE ( MM; CONCATENATE ( ":"; SS ) ) )) = "::"

            ;BLANK()

            ;CONCATENATE (HH;CONCATENATE ( ":"; CONCATENATE ( MM; CONCATENATE ( ":"; SS ) ) )))

148:00:13

Kind regards,

Wouter Kessener

eyJrIjoiZGNhMjc2NWYtMTQwZi00NzY1LTgzNTgtNWNlMWRkODMzY2MwIiwidCI6IjljYTBlMTY4LWIxYmQtNDYyOC1iYjg2LTc0NTZhYjI5NTJmOSIsImMiOjh9

[RichardV]

Great Wouter!

Thanks for your post, it helped me a lot. Let me return the favor by making an addition. What I noticed is that you had to make multiple measures in order to create the one measure you want. Below you can find my DAX formula, in which you only need one input variable in seconds, in order to create the measure you want in HH:MM:SS format. You do not have to make several other measures. I know the formula can be made less complex, but hey, it works.

The only thing you have to do, is replace [Input variable in seconds] into a variable of your choice. You can use the find/replace option in Word for this.

Measure in HH:MM:SS format =

IF(         CONCATENATE (IF ( LEN ( INT ( [Input variable in seconds] / 3600 )  )     = 1;CONCATENATE ( "0"; INT ( [Input variable in seconds] / 3600 )       );CONCATENATE ( ""; INT ( [Input variable in seconds] / 3600 )        ));CONCATENATE ( ":"; CONCATENATE ( IF ( LEN ( INT ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ) / 60) ) = 1;CONCATENATE ( "0"; INT ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ) / 60)   );CONCATENATE ( ""; INT ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ) / 60)     )); CONCATENATE ( ":"; IF ( LEN ( ROUNDUP(MOD ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ); 60 );0) )   = 1;CONCATENATE ( "0"; ROUNDUP(MOD ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ); 60 );0)    );CONCATENATE ( ""; ROUNDUP(MOD ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ); 60 );0) )) ) ) )) = "::"

            ;BLANK()

            ;CONCATENATE (IF ( LEN ( INT ( [Input variable in seconds] / 3600 )  )     = 1;CONCATENATE ( "0"; INT ( [Input variable in seconds] / 3600 )       );CONCATENATE ( ""; INT ( [Input variable in seconds] / 3600 )        ));CONCATENATE ( ":"; CONCATENATE ( IF ( LEN ( INT ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ) / 60) ) = 1;CONCATENATE ( "0"; INT ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ) / 60)   );CONCATENATE ( ""; INT ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ) / 60)     )); CONCATENATE ( ":"; IF ( LEN ( ROUNDUP(MOD ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ); 60 );0) )   = 1;CONCATENATE ( "0"; ROUNDUP(MOD ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ); 60 );0)    );CONCATENATE ( ""; ROUNDUP(MOD ( MOD( [Input variable in seconds] - ( INT ( [Input variable in seconds] / 3600 )  * 3600 );3600 ); 60 );0) )) ) ) )))

[Wouter]

Hello Richard,

Thank you, glad it helped you out.

I’m intrigued by your comment, read it twice, trying to figure out if I can learn something.

I probably can, don’t quit now where to look yet.

What is the benefit of not using variables? Is it quicker?

 KR Wouter