cancel
Showing results 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.

Frequent Visitor

## Calculate Duration in hh:mm:ss

I am trying to figure out how to format time from either seconds, minutes, hours or serial number to HH:MM:SS format that will calculate in charts and tables. I have read a lot of the previous post on the issue but there doesn't seem to be a very detailed way to do this. I am new to Power BI.

```// We start with a duration in number of seconds
VAR Duration = [Duration in Seconds]
// There are 3,600 seconds in an hour
VAR Hours =
INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
INT ( MOD( Duration - ( 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( Duration - ( 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
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
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 ) ) )
)```

It doesn't really explain, in detail, how to convert seconds to duration, it doesn't explain if this needs to be a new column or a new measure. Very confusing. So, if somebody could just post the formula to convert seconds, (seconds are formated at decimal number, however, I can change it to whatever it needs to be), to HH:MM:SS, that would be awesome.

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

The DAX you posted can convert number into HH:MM:SS format. It requires to use a calcuated column for running the formula. See the example below.

Community Support

Additionally, it should be noted that when you change duration to HH:MM:SS, it would become text type and can't be calculated.

Best Regards

Maggie

Frequent Visitor

What good is a time calculation if you can't calculate it? I will accept this as a solution under the pretense that you guys are working on a way to do time calculations. Thanks for your help as always.

6 REPLIES 6

Hiya

I worked out how to convert a number of seconds when it is in a file as a whole number to hh:mm:ss format. You need to use a new column from the modelling tab, then use the following formula:

Column name = RIGHT ( "0" & INT ( ‘File’[Sheet Name] / 3600 ), 2 )

& ":" &

RIGHT (  "0" & INT ( ( ‘File’[Sheet Name] - INT ( ‘File’[Sheet Name] / 3600 ) * 3600 ) / 60 ), 2 )

& ":" &

RIGHT ( "0" & MOD ( ‘File’[Sheet Name], 60 ), 2)

This will return a string of whole numbers in seconds, ie 4789 as 01:19:45

Good luck!

Regular Visitor

Hi everyone,

the solution that I made work for me to create (Score) Card visualizations  of Elapsed/ Duration Times in HH:MM:SS format capitalizes on AnneC's formula but takes a different approach (Do Measures First then Format in HH:MM:SS) with the following logic:

1. In the Power Query Editor - Ensure your  elapsed times/ duaration times data is calculated in SECONDS and the Column containing that data is of Data Type: Whole Number (Transform tab);

2. As it is a Score Card it has to be based on a calculation/measure first, e.g. median, average, percentile, etc. Otherwise, it won't work. In my case, I use 90th percentile  for which I utilize the formula: Measure =

PERCENTILE.INC(QueryName[ElapsedTime in Seconds], 0.9).
3. Opposite to AnnC's approach, I am creating Measure instead of a New Column to calculate the 90th percentile (or average, or median, etc.). This is how I keep the data format being number/int and thus allows for calculations. Otherwise, a text format won't allow this.
4. Then, I create another Measure on the top of the 90th percentile Measure exploring the formula provided by AnnC using the follwing formula:
90th TOT 1stDispatchedTo1stEnroute Seconds = RIGHT ( "0" & INT ( PERCENTILE.INC(QueryRedNMxSOCsResponseTimes[TOT 1stDispatched To 1stEnrouteTime Seconds], 0.9) / 3600 ), 2 )
& ":" &
RIGHT ( "0" & INT ( ( PERCENTILE.INC(QueryRedNMxSOCsResponseTimes[TOT 1stDispatched To 1stEnrouteTime Seconds], 0.9) - INT ( PERCENTILE.INC(QueryRedNMxSOCsResponseTimes[TOT 1stDispatched To 1stEnrouteTime Seconds], 0.9) / 3600 ) * 3600 ) / 60 ), 2 )
& ":" &
RIGHT ( "0" & MOD ( PERCENTILE.INC(QueryRedNMxSOCsResponseTimes[TOT 1stDispatched To 1stEnrouteTime Seconds], 0.9), 60 ), 2)
You can see the 90th percentile of the elapsed/duration times represented into an HH:MM:SS format by the Score Cards in the middle of the canvas. Try it and I hope it will work for you as well. Thanks.
Community Support

Additionally, it should be noted that when you change duration to HH:MM:SS, it would become text type and can't be calculated.

Best Regards

Maggie

Frequent Visitor

What good is a time calculation if you can't calculate it? I will accept this as a solution under the pretense that you guys are working on a way to do time calculations. Thanks for your help as always.

Community Support

How would you like to calculate?

If you want to convert a time in text format to a time in datetime format, you can use the following function

TIMEVALUE

Best Regards

Maggie

Anonymous
Not applicable

The DAX you posted can convert number into HH:MM:SS format. It requires to use a calcuated column for running the formula. See the example below.

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors