Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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

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 ?

1 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:

 2020-07-03_9-07-43.png

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

View solution in original post

6 REPLIES 6
BA_Pete
Super User
Super User

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 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

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

 

@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:

 2020-07-03_9-07-43.png

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

Anonymous
Not applicable

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

Capturar3.PNGDo you know why the yellow lines below are giving the wrong value?
Anonymous
Not applicable

@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 🙂 

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.

jdbuchanan71_0-1593793411918.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.