Seconds 2 HHMMSS

Regular Visitor
2377 Views
Highlighted
Regular Visitor
Posts: 28
Registered: ‎09-18-2015

Seconds 2 HHMMSS

How many hours ins 532813 seconds.....

 

 

# Time spent HH:MMSmiley FrustratedS =

 

// 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:MMSmiley FrustratedS

 

 

RETURN

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

            ;BLANK()

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

 

 

 

 

148:00:13

Kind regards,

 

Wouter Kessener

 

Attachment
Regular Visitor
Posts: 18
Registered: ‎02-02-2017

Re: Seconds 2 HHMMSS

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:MMSmiley FrustratedS 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:MMSmiley FrustratedS 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) )) ) ) )))

 

Regular Visitor
Posts: 28
Registered: ‎09-18-2015

Re: Seconds 2 HHMMSS

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