Forum Discussion

shannon8000's avatar
shannon8000
Frequent Visitor
2 years ago

PBI seconds conversion to hh:mm

Hello,

I've tried two different formulas I've found in these forums.  At first it looks like it is working but then the math isn't mathing when reviewing the output.

In the below example, you can see 7200 seconds is calclating to 1 hour however is should be 2 hours.  You can then see there are several 3600 second items that correctly display as 1 hour.  I exported to excel and did an Excel conversion and found several instances where the PBI calculation appears correctly and in others not with no identifiable pattern.   Of 14 instances of 14400 seconds, 3 PBI shows as 2 hours and the remaining correct at 4 hours.  

 

I've tried both formulas below:

Duration_ACTVTY =
VAR Duration = [ACTVTY_TTL_SEC]
// There are 3,600 seconds in an hour
VAR Hours =
INT ( [ACTVTY_TTL_SEC]/ 3600)
// There are 60 seconds in a minute
VAR Minutes =
INT ( MOD( [ACTVTY_TTL_SEC] - ( 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( [ACTVTY_TTL_SEC] - ( 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 ) ) )
)
 
and
hhmm test activity =
VAR hours =
    ROUNDDOWN ( [ACTVTY_TTL_SEC] / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [ACTVTY_TTL_SEC], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [ACTVTY_TTL_SEC], 60 ) )

RETURN
    FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")
 
Any ideas?  I checked the source data and there aren't any instances where the seconds are configured any differently than the others.  TIA 🙂
Tips and Tricks

3 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi shannon8000 

    Here is an example

    and here is the formula

    Total duration =
    var _seconds = SUM(Sheet1[ACTVTY_TTL_SEC])
    var _minutes = INT(_seconds / 60)
    var _RemainingSeconds = MOD(_seconds, 60)
    var _Hours = INT(_minutes / 60)
    var _RemainingMinutes = MOD(_minutes, 60)
    var _Days = INT(_Hours / 24)
    var _RemainingHours = MOD(_Hours, 24)
    return
    _Days & " D : " &
    _RemainingHours & " H : " &
    _RemainingMinutes & " M : " &
    _RemainingSeconds & " S  "
     
     
    • shannon8000's avatar
      shannon8000
      Frequent Visitor
      2 years ago

      Hi Amine!

       

      I applied as recommended and the output was interesing.

       

      I will poke around some more to see what is happening.

      • aj1973's avatar
        aj1973
        Icon for Community Champion rankCommunity Champion
        2 years ago

        You have a relationship problem in your Matrix or Table visual.