Hours over 24h (like excel 37:30:55)

[Anonymous]

Hi all, 

I need to calculate the SUM of hours and display it as excel format 37:30:55, however when I perfom the sum when it over pass 24, it starts return the count to zero. example if the sum of the hours is 26hours and 15 min is wrongly returning as 2:15:00 ideally it should displayed as 26:15:00. 

If I change the data type to duration, it displays as 1.02:15:00. Is there any way to return it as it's possible in excel format as 26:15:00 ?

[BA_Pete]

Hi @Anonymous ,

Have you tried the format function?

myHours =
FORMAT(
  SUM(table[hours]),
  "hh:mm"
)

Not sure this will work for you but worth a shot.

Pete 

[Anonymous]

@BA_Pete It's return correct if is under 24, over 24 has it restart from zero. 

[jdbuchanan71 · Accepted solution]

@Anonymous 

What does your duration data look like in the table?  If you have the values in seconds you can use something like this.

Call Duration:=
VAR _Seconds = [Call Duration Seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )
RETURN
    IF (
        NOT ISBLANK ( [Call Duration Seconds] ),
            FORMAT ( _Hours, "00" ) & ":" &
            FORMAT ( _RemainingMinutes, "00" ) & ":" &
            FORMAT ( _RemainingSeconds, "00" )
    )

When the time goes over 24 hours the measure looks like this:

 

[Call Duration Seconds] used in the first var is just the sum of the seconds column on the call_log table.

[Anonymous]

Do you know why the yellow lines below are giving the wrong value?

Do you know why the yellow lines below are giving the wrong value?

[Anonymous]

@jdbuchanan71 it worked perfectly. 

My hours was in decimal , so I've converted it to seconds (multiplying by 3600) and then used it on the formula. 

Thanks so much for your help.  you saved 🙂 

[jdbuchanan71]

If your duration is more like hh:mm:ss you can get the duration in seconds by adding a calculated column that is the duration / (1/86400).  86400 is the number of seconds in a day so taking 36:10:15 returns 130215 just like it would in excel.

Then you can sum that to get the [Duration in seconds] measure used in my example.