Convert from Seconds to Minutes with a DAX Measure
I was recently doing some consulting work for a customer and they had a column which contained seconds and whilst this is useful it is better to have it shown as Minutes and Seconds, which is easier to relate and compare.
Below I will show you how I came up with the DAX measure which converts it from seconds to minutes, and I will be using my Parkrun sample dataset. As shown below I have a table which shows the total seconds
Below is my DAX measure with an explanation on how I got to the measure
Secs to Mins = VAR TimeSecs = SUM ( 'ParkRun'[Total Seconds] )+1 VAR TimeMins = DIVIDE ( TimeSecs, 60 ) VAR JustSeconds = TimeMins  TRUNC ( TimeMins ) VAR JustMinutes = TimeMins  JustSeconds VAR JustSecondsInTime = JustSeconds * 60 VAR MySeconds2 = IF ( JustSecondsInTime < 10, FORMAT ( "0" & JustSecondsInTime, "00" ), JustSecondsInTime ) RETURN VALUE ( JustMinutes & "." & LEFT ( MySeconds2, 2 ) )

Line 1
 This is where I created the measure with a name

Lines 2 – 3
 This is where I am summing up all the Total Seconds

Lines 4 – 5
 I am creating a variable called TimeMins and then dividing the seconds by 60 to get the minutes.

Line 6 – 7
 I am creating a variable called JustSeconds, and I am using the TRUN DAX function, so that this returns the remainder of the variable TimeMins, which is currently stored as a decimal.
 NOTE: The above means that the remainder is stored as decimal point out of 100. So if the remainder is 0.50 that translates to 30 seconds. Which is shown below.

Line 8 – 9
 I am creating a variable called JustMinutes, where all that I want is the Minutes, so I do this by subtracting the TimeMins from JustSeconds

Line 10
 I am creating a variable called JustSecondsInTime, and what I am doing is converting the seconds from a decimal value back to time.
 What this means is that if the decimal value was 0.50, the variable JustSecondsInTime will be converted to 0.30 which represents 30 seconds.

Line 11 – 12
 I am creating a variable called MySeconds2, and what I am doing here is to ensure that if it is less than 10 seconds to add in a zero before the second number. This is so that it will be displayed as expected.
 As shown below you can see where the seconds are less than 10 it is displaying correctly.
 When looking at the image above it does appear that the seconds is not being displayed correctly. This will be resolved in the next step below.

Line 13 – 14
 Now I am closing off the variable section to return the measure.
 I begin with the DAX function called VALUE, this is because I want my DAX to be a measure in my model.
 I then put in my variable called JustMinutes which is the minutes from the seconds that I defined in lines 8 & 9

Next, I put in a dot “.” This is to ensure that the VALUE will still work because it expects a value in the format of 99.22
 If I had to change it from a dot to the colon the VALUE function would return an error.

I get the seconds to display correctly by using the DAX function LEFT ( MySeconds2, 2 )
 What I am doing here is using the LEFT DAX Function to get the first 2 values starting from the left hand side. Which results in displaying the seconds
Now when I take my measure and put it into a table I get the Minutes and seconds displaying as expected.
I found that I really had to use the DAX variables to get this pattern to work as expected. With all the screenshots above, I changed the return function to the particular DAX variable that I was working on to ensure that it was returning the value that I was expecting.
If you have any questions or comments please leave them in the section below. I do hope that you found this useful.
[…] Convert from Seconds to Minutes with a DAX Measure (@GilbertQue) […]
Thank you, explained beautifully
Thank you for the kind words.
Hello Team,
Can you please help me to convert Minutes to Seconds ( I want second as whole number)
Hi Mohan,
What you can do is to return the variable called JustSeconds and that will give you the seconds as a whole number.
Thanks for sharing this information!
How can we use “HH:MM:SS” as an axis value for a line/bar/area chart?
Hi there,
The challenge is that I wanted the result to be a data type of number so that it could be used as a measure.
Would you be looking to rather use the output with hours, mins, secs?
Yes, exactly. Thanks for any help / insight you can share!
Let me take a look a little later and see what I can do
@Michael
you can use a calculated column:
Secs to Time =
VAR Hours = QUOTIENT ( ‘ParkRun'[Total Seconds], 3600 )
VAR Rest = MOD ( ‘ParkRun'[Total Seconds], 3600 )
VAR Minutes = QUOTIENT ( Rest, 60 )
VAR Seconds = MOD( Rest, 60 )
RETURN
TIME ( Hours, Minutes, Seconds )
Thanks Frank, that would most certainly work.
The challenge would be when you want to use it as a measure.
You could do it with the following code below, due to a number value only having one point.
SecMinHour =
VAR TimeSecs =
SUM ( 'ParkRun'[Total Seconds] ) + 1
VAR TimeMins =
DIVIDE ( TimeSecs, 60 )
VAR JustSeconds =
TimeMins  TRUNC ( TimeMins )
VAR JustMinutes = TimeMins  JustSeconds
VAR JustSecondsInTime = JustSeconds * 60
VAR MySeconds2 =
IF (
JustSecondsInTime < 10, FORMAT ( "0" & JustSecondsInTime, "00" ), JustSecondsInTime ) VAR JustHours = VAR HourTest = DIVIDE ( JustMinutes, 60 ) RETURN IF ( HourTest < 1, 0, HourTest ) VAR JustHours2 = DIVIDE ( JustMinutes, 60 ) VAR MoreThan1Hour = IF ( JustMinutes < 60, VALUE ( JustMinutes & "." & LEFT ( MySeconds2, 2 ) ), VALUE ( LEFT ( JustHours, 2 ) & "." & JustMinutes ) ) RETURN MoreThan1Hour
[00:06:24] how can i convert this format of hours to seconds?
another question:
how can i get the average of a colones contain:
[00:06:00]
[00:06:44]
[00:05:00]
[00:03:00]
[00:02:00]
Thank you in advance
Hi there
In your example is that time in Minutes & seconds?
If so I would first change it all to seconds in Power Query, which would then allow me to convert it from seconds to minutes.
For your Average I would have it all in seconds, calculate the average My Average = DIVIDE(SUM(Seconds),COUNTROWS(Table))
I am using the below which you posted, and it works great for me in displaying the Minutes and Seconds, and this allows it to also be calculated in some matrixes and graphs im using which is great.
However when this gets greater then 60 minutes its gets an error saying cannot convert text type to number – any help!
SecMinHour =
VAR TimeSecs =
SUM ( ‘ParkRun'[Total Seconds] ) + 1
VAR TimeMins =
DIVIDE ( TimeSecs, 60 )
VAR JustSeconds =
TimeMins – TRUNC ( TimeMins )
VAR JustMinutes = TimeMins – JustSeconds
VAR JustSecondsInTime = JustSeconds * 60
VAR MySeconds2 =
IF (
JustSecondsInTime < 10, FORMAT ( "0" & JustSecondsInTime, "00" ), JustSecondsInTime ) VAR JustHours = VAR HourTest = DIVIDE ( JustMinutes, 60 ) RETURN IF ( HourTest < 1, 0, HourTest ) VAR JustHours2 = DIVIDE ( JustMinutes, 60 ) VAR MoreThan1Hour = IF ( JustMinutes < 60, VALUE ( JustMinutes & "." & LEFT ( MySeconds2, 2 ) ), VALUE ( LEFT ( JustHours, 2 ) & "." & JustMinutes ) ) RETURN MoreThan1Hour
I have used the below which you posted (adjusted to pick up the seconds from a measure) which worked brilliantly when i was looking at Minutes and seconds, however when the total time goes over 60 minutes it spits out an error of cannot convert value of type text to number. It works fine when dropped into a card and matrix when under 60mins however over this is when the error appears, any help?
SecMinHour =
VAR TimeSecs =
Dials[TTT (S)]
VAR TimeMins =
DIVIDE ( TimeSecs, 60 )
VAR JustSeconds =
TimeMins – TRUNC ( TimeMins )
VAR JustMinutes = TimeMins – JustSeconds
VAR JustSecondsInTime = JustSeconds * 60
VAR MySeconds2 =
IF (
JustSecondsInTime < 10, FORMAT ( "0" & JustSecondsInTime, "00" ), JustSecondsInTime ) VAR JustHours = VAR HourTest = DIVIDE ( JustMinutes, 60 ) RETURN IF ( HourTest < 1, 0, HourTest ) VAR JustHours2 = DIVIDE ( JustMinutes, 60 ) VAR MoreThan1Hour = IF ( JustMinutes < 60, VALUE ( JustMinutes & "." & LEFT ( MySeconds2, 2 ) ), VALUE ( LEFT ( JustHours, 2 ) & "." & JustMinutes ) ) RETURN MoreThan1Hour
Hi Christopher,
This would be because I was using it in a table and that is why it was not working!
I would have to spend some time getting to figure out what to do if it is longer than 60 minutes. It could be possible to use the Variable “JustSecondsInTime” and if it is larger than 60, then change it to the requirement?