cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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:MMSmiley FrustratedS.  Would something like this work, or is there a better way?

 

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

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator Eric_Zhang
Moderator

Re: formatting time value to hh:mm:ss

@guyinkalamazoo

 

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

18 REPLIES 18
jbocachica Member
Member

Re: formatting time value to hh:mm:ss

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

Re: formatting time value to hh:mm:ss

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

jbocachica Member
Member

Re: formatting time value to hh:mm:ss

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

Re: formatting time value to hh:mm:ss

Unfortunately that did not work.

jbocachica Member
Member

Re: formatting time value to hh:mm:ss

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

Re: formatting time value to hh:mm:ss

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

jbocachica Member
Member

Re: formatting time value to hh:mm:ss

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

Re: formatting time value to hh:mm:ss

@guyinkalamazoo

 

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

tmilu Visitor
Visitor

Re: formatting time value to hh:mm:ss

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 )

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 54 members 1,174 guests
Please welcome our newest community members: