Wouter's avatar
Wouter
Resolver I
9 years ago

Seconds 2 HHMMSS

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

 

Other

2 Replies

  • RichardV's avatar
    RichardV
    Advocate IV
    9 years ago

    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's avatar
      Wouter
      Resolver I
      9 years ago

      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