cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

formatting time value to hh:mm:ss

I hope I am posting this to the right location as I am developing a new report.  Anyway, this is coming out of our telephone DB system and one column represented is Duration, which is an int column and represents the call in the number of seconds.  I would like to convert and format that to HH:MM:SS.  Would something like this work, or is there a better way?

 

New Duration = FORMAT(TableName[Duration]/60, "HH:mm:ss")

 

Thanks

1 ACCEPTED SOLUTION

@Anonymous

 

Try a DAX as below.

fmtCol = 
RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )

Or deal with the format in query.

select Duration, convert(varchar(10),DATEADD(second,Duration,0),108) fmtSecs from t1

Capture.PNG 

View solution in original post

20 REPLIES 20
dreamon
Regular Visitor

New Duration = FORMAT([Duration], "nn:ss")

mm works only with HH:mm:ss

 

See  https://dax.guide/format/

 

jbocachica
Resolver II
Resolver II

Hi, you are just returning the minutes, you must return the entire number.

 

New Duration = format(((TableName[Duration] / 60)/60)/24, "HH:mm:ss")

 

Regards

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co
Anonymous
Not applicable

The column value from SQL is an integer in the number of seconds so wouldnt / 60 return how many minutes, seconds, etc (except now I know what you are saying whereas if the number of minutes is more than 60).  However, I get an error when trying to use FORMAT for the column in that FORMAT cannot be used with a calculated column. 

 

11-2-2016 3-12-55 PM.png

 

 

 

I also found this article: http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Hi, you must go to Options and then find the DirectQuery options and enable the checkbox that says "Allow unrestricted measures in DirectQuery mode" and try again.

 

Regards

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co
Anonymous
Not applicable

Unfortunately that did not work.

Uhhh... which kind of datasource are you using?
John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co
Anonymous
Not applicable

Straight up SQL Server data table.  Nothing special at all.

@Anonymous

 

Try a DAX as below.

fmtCol = 
RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )

Or deal with the format in query.

select Duration, convert(varchar(10),DATEADD(second,Duration,0),108) fmtSecs from t1

Capture.PNG 

This is what I am looking for.  What would the calculation be if instead of converting minutes it were hours?

Anonymous
Not applicable

Hi @Eric_Zhang

 

I kinda stuck with the similar error when converting the seconds to duration (HH : MM : SS) format using the following query.

 

Select TimeinSeconds, from_unixtime(TimeinSeconds,"HH:mm:ss") as Duration
From Tablename

When I execute the above query in Impala, I'm getting the result as expected and the same query when I use to load the data into Power BI, I'm getting this "Token Comma Expected" error. Can you help me with this, please?

@Eric_Zhang

 

Hi Eric,

 

Your code for Power BI solution works well... the only issue Minutes displayed out of 100 min and not as standard 60 min =o?

 

Any advice?

 

Thanks

 

Abduvali

Had a question about D:HH:MM:SS, hopefully this helps.

 

Dtime (meas) = 
VAR vDur = <<enter object as seconds>>
    RETURN INT(vDur/86400) & ":" &                       //Days
	RIGHT("0" & INT(MOD(vDur/3600,24)),2) & ":" &    //Hours 
	RIGHT("0" & INT(MOD(vDur/60,60)),2) & ":" &      //Minutes
	RIGHT("0" & INT(MOD(vDur,60)),2)                 //Seconds

Hi, this post is in spanish but will be helpful in this case.

 

http://blog.iwco.co/2018/03/28/formato-duracion-power-bi/

 

Regards

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co

just a click on a translate button and this is so helpful thanks a lot.

 

PS: use @jbocachicasuggested website, if you want to convert from seconds to this format HH:mm:ss, I used the following expression using DAX. I just created a new measure using the existing measure in seconds, because my seconds rae in a measure not a column, well I am getting my Data from Analysis Services.

 

Measure :=  FORMAT(FactTable[MeasureName in seconds]/86400, "HH:mm:ss") 

By magic you have it in one single line haha.

 

Read about Format Function here ==> https://msdn.microsoft.com/en-us/query-bi/dax/format-function-dax

@Eric_Zhang

 

Thank you!!! You are officially a legend =D

 

Worked really well to convert seconds into time format from a calculation in a measure!!!

 

To be honest, there is a big lack of time formatting options in Power BI, I would love to see a function that would allow us to display time as a total number of hours like in Excel that would make life so much easier.

 

but anyway rant is over =D thanks again!!!

I'm pretty new to DAX so it's possible that I'm doing something wrong, but your code was calculating the incorrect seconds for me and I made the following changes to get it to work:

 

 

fmtCol = 
RIGHT ( "0" & INT ( TableName[Duration]/ 3600 ), 2 ) & ":" & RIGHT ( "0" & INT ( (TableName[Duration]- INT (TableName[Duration]/ 3600 ) * 3600 ) / 60 ), 2 ) & ":" & RIGHT ( "0" & INT(MOD(MOD (TableName[Duration], 3600),60)), 2 )

 

 

@Eric_Zhang
Hello I was able to create the DAX as a calculated column not a measure when creating as a measure I get the following error:

 

"A single value for column duration in table dialingresults cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

Either way when I use the code provided:

 

Time =
RIGHT ( "0" & INT ( DialingResults[Duration]/ 3600 ), 2 )

    & ":"  

  & RIGHT (     

   "0"         

   & INT ( (DialingResults [Duration]- INT (DialingResults [Duration]/ 3600 ) * 3600 ) / 60 ), 

       2  

  )  

  & ":"  

 & RIGHT ( "0" & INT(MOD(MOD (DialingResults [Duration], 3600),60)), 2 )

 

It is giving me mis-calculations see attached image.

5-15-2018 12-30-50 PM.png

Hi

 

I am also experiencing this, have you found any solution for this?

 

Thanks 

Guys I think I got it.

 

You need a field that is just seconds and use this code as a measure.

 

Time = FORMAT(INT(

IF(MOD([Seconds]|60)=60|0|MOD([Seconds]|60)) +

IF(MOD(INT([Seconds]/60)|60)=60|0|MOD(INT([Seconds]/60)|60)*100) +

INT([Seconds]/3600)*10000)| "00:00:00")

 

Seconds = the field that contains seconds.

Well, to use certain functions you must Import your data and not use Direct Query 😞
John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.