cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JClishe
Helper II
Helper II

How to convert seconds to hh:mm:ss

I'm pulling in data that's formatted as seconds and I'm trying to convert it to hh:mm:ss but I can't figure it out. I've searched here and found several other threads with similar questions but even after reading the suggestions I still can't get it right.

 

I've created a new column that takes the original column and divides by 3600, and formatted that new column as a decimal. That works, but it's not the format that I want. When I try formatting either the new column I created, or the original column, using any of the time formats the display is never correct. 

 

Can anyone explain how to do this in very simple terms? I don't have much experience with PBI Desktop, which is probably obvious. 🙂 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

In the Query Editor,

open your query,

go to tab "Add Column"

choose "Custom Column"

enter column name and formula

choose OK

adjust the code by adding ", type time".

 

Adding a custom column in the Query Editor.png

Specializing in Power Query Formula Language (M)

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Hi, 

 

I have prepared a detailed article for converting Seconds to hh:mm:ss format in power bi.

 

https://www.powerbitalks.com/2019/12/convert-seconds-to-hhmmss-format.html

 

It has complete information with step by step solution.

 

Regards,

Kaushlendra Mishra 

www.powerbitalks.com 

Anonymous
Not applicable

The easiest way to do this will be to make sure the Column is formatted into the data type of "Time".  You can change the data type from the ribbon under the modelling tab.  The process of doing this is to select the column in your data table (easiest from the Data View) and change the data type to "Time".  Once the data type is changed to time, you can then change the Format to "hh:mm:ss" from the dropdown directly below.

 

Common issues you will have here is getting Power BI to appropriately assign your value to the right level of detail (i.e. classify it as seconds rather than minutes or hours).  You have already acknowledged a desire to divide by 3600, so its likely you are on the right track.  I'm not sure why you are using 3600, but will assume it relates to the format your time data is presently in.

 

To clarify how you can ensure the best transition.  DateTime is stored as a decimal number, the whole number component of the value makes up the Date.  The Decimal number is the Time component, where 0 is Midnight and 1 the other midnight, thus 0.5 is midday.  1 Second, as a whole number, could be converted to 1 second in DateTime by dividing it by 24 (Hours in Day), dividing again by 60 (Minutes in Hour) and then again by 60 (Seconds in minutes).  To simplify, take your seconds as a whole number and divide by 86,400  (24 x 60 x 60)

 

This soluntion does not work for me, my column is in seconds 

 

When i tried your solution, it does not aggregate

I've changed the column data type to Time but as I indicated in my original post, it doesn't display correctly. When I use Time and select hh:mm:ss or any of the other options, I just get all zeros.

 

To your question of why I divided by 3600, it's because dividing seconds by that number will give me hours in decimals. This is not what I ultimately want, but it is a stop gap measure until I can figure out how to get it displayed as h:mm:ss. So instead of a field that says 5276 seconds, now I have a field that says 1.46 hours, which is much easier for me to interpret. Again, this isn't what I want, but it's better than seconds. 

MarcelBeug
Community Champion
Community Champion

If a Power Query solution is also OK:

 

let
    Source = #table(type table[Seconds = Int64.Type],{{5276}, {12675}, {76538}}),
    #"Added Custom" = Table.AddColumn(Source, "Time", each #time(0,0,0) + #duration(0,0,0,[Seconds]), type time)
in
    #"Added Custom"
Specializing in Power Query Formula Language (M)

How would I use this?

MarcelBeug
Community Champion
Community Champion

In the Query Editor,

open your query,

go to tab "Add Column"

choose "Custom Column"

enter column name and formula

choose OK

adjust the code by adding ", type time".

 

Adding a custom column in the Query Editor.png

Specializing in Power Query Formula Language (M)

View solution in original post

Thanks marcel this was helpful 

Hi,

 

I have seconds value field which calculated thru SQL, I need to know how to convert this field into hh:mm:ss format and then doing sum on the result field.

 

We tried thru SQL using convert and varchar function, but after converion I was unable to do SUM on the field.

 

Can any one help me on how to get this..

How to get sum on hh:mm:ss second value field? I have field which is in seconds, when i tryied changing the data time to TIME... I am getting warning message to Fix this like....Is their any way to convert Seconds field value to HH:mm:ss format and then again doing the Sum on hh:mm:ss field in POWER BI?

 

I tried with SQL but still not getting it....please help

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.