Converting whole number to duration

[Novice_92]

Hi all,

I'm in the process of building a dashboard, part of which will detail how long a user has been in various states, ACD, ACW etc.

Current data type is whole number for time (seconds) and I'm looking for a way to convert this into hours, minutes & seconds and for this not to be converted into days when 24 hours is hit.

Any help would be much apperiated.

[v-jingzhang · Accepted solution]

Hi @Novice_92 

Here is one method with calculated column.

Duration = 
var vHour = INT('Table'[Seconds]/3600)
var vMinute = INT(MOD('Table'[Seconds],3600)/60)
var vSecond = MOD(MOD('Table'[Seconds],3600),60)
return
vHour&":"&vMinute&":"&vSecond

You can make similar calculations to create a measure or a custom column with Power Query. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[arichard19]

I would personally do this in Power Query but you can also do it via Calculated Columns or DAX Measures -

When importing the data into Power Query, depending on exactly what date columns it contains, you could use the following PowerQuery MashUp Code functions to convert the seconds into different levels of duration.
https://docs.microsoft.com/en-us/powerquery-m/datetime-functions

You could also just use basic math, and create custom columns in Power Query so that you do exercises such as ->
Minutes = Column[Seconds] / 60 -> Hours = Column[Minutes] / 60 -> Etc...

The same can be done using calculated columns ->
Minutes = Column[Seconds] / 60 -> Hours = Column[Minutes] / 60 -> Etc...