topic Re: formatting time value to hh:mm:ss in Developer

formatting time value to hh:mm:ss

Anonymous — Wed, 02 Nov 2016 18:43:36 GMT

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

Re: formatting time value to hh:mm:ss

jbocachica — Wed, 02 Nov 2016 19:09:16 GMT

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

Re: formatting time value to hh:mm:ss

Anonymous — Wed, 02 Nov 2016 19:13:51 GMT

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.

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

Re: formatting time value to hh:mm:ss

jbocachica — Wed, 02 Nov 2016 19:48:19 GMT

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

Re: formatting time value to hh:mm:ss

Anonymous — Thu, 03 Nov 2016 11:48:43 GMT

Unfortunately that did not work.

Re: formatting time value to hh:mm:ss

jbocachica — Thu, 03 Nov 2016 14:42:53 GMT

Uhhh... which kind of datasource are you using?

Re: formatting time value to hh:mm:ss

Anonymous — Thu, 03 Nov 2016 17:56:55 GMT

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

Re: formatting time value to hh:mm:ss

jbocachica — Fri, 04 Nov 2016 21:17:20 GMT

Well, to use certain functions you must Import your data and not use Direct Query 😞

Re: formatting time value to hh:mm:ss

Eric_Zhang — Mon, 07 Nov 2016 07:54:51 GMT

@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

Re: formatting time value to hh:mm:ss

tmilu — Mon, 27 Mar 2017 20:30:35 GMT

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 )

Re: formatting time value to hh:mm:ss

Abduvali — Tue, 11 Apr 2017 11:14:46 GMT

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

Re: formatting time value to hh:mm:ss

Abduvali — Wed, 12 Apr 2017 13:20:47 GMT

@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

Re: formatting time value to hh:mm:ss

Anonymous — Thu, 15 Mar 2018 06:10:55 GMT

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

Re: formatting time value to hh:mm:ss

jbocachica — Wed, 04 Apr 2018 16:58:09 GMT

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

Re: formatting time value to hh:mm:ss

bizzybisnette — Tue, 15 May 2018 17:04:35 GMT

@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 ),

)

& ":"

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

It is giving me mis-calculations see attached image.

Re: formatting time value to hh:mm:ss

Mk2018 — Tue, 12 Jun 2018 14:11:23 GMT

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

Thanks

Re: formatting time value to hh:mm:ss

bizzybisnette — Wed, 15 Aug 2018 21:33:37 GMT

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.

Re: formatting time value to hh:mm:ss

ydaoud — Thu, 16 Aug 2018 10:37:15 GMT

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

Re: formatting time value to hh:mm:ss

Anonymous — Tue, 21 Aug 2018 10:14:46 GMT

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?

Re: formatting time value to hh:mm:ss

dreamon — Wed, 01 Dec 2021 15:22:59 GMT

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

mm works only with HH:mm:ss

See https://dax.guide/format/