PBI seconds conversion to hh:mm

[shannon8000]

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 🙂

[aj1973]

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]

Hi Amine!

I applied as recommended and the output was interesing.

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

[aj1973]

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