# Converting 1000000 seconds in power query does not give the right days

#### Lisa77

##### New member
Hi please see attachment, I need to convert seconds to dd:hh:mm:ss. PQ does not calculate the correct days when it reaches a million seconds it seems. Any help would be appreciated. Thank you.

#### Attachments

• Time Test for Help.xlsx
181.8 KB · Views: 7

#### alansidman

##### Member
How to use Duration function in PQ-->https://efficiency365.com/2018/08/31/power-bi-seconds-to-duration/

Code:
``````let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each Text.EndsWith([Name], "_Report")),
#"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Service Target Runtime"}, {"Service Target Runtime"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Content",{{"Service Target Runtime", "Service Target Runtime Seconds"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Name"}),
in

#### alansidman

##### Member
Also, just noted that your manual calculation you are dividing by 8 when you should be dividing by 24 as there are 24 hours in a day not 8.

#### Attachments

• Time Test for Help.xlsx
251.5 KB · Views: 5

#### Lisa77

##### New member
Hi thank you for the code. I will check it out. I'm dividing by 8 as we are required to measure the time a ticket is logged to the time it is closed within business hours. Will your code calculate business hours only?

#### alansidman

##### Member
No. I calculates Actual Days.

#### p45cal

##### Super Moderator
Staff member
Lisa77, your calculations give odd results for low numbers of seconds; 72 secs (1 min 12 secs) comes out as 3 mins 36 seconds.
In an Excel sheet it's difficult to display dd:hh:mm:ss because that format won't show the days part with more than a month's days (31). It would be nice if it could - it does allow for example, to show more than 24 hours if there are greater than 24 hours using square brackets in a format such as: [hh]:mm:ss, but it doesn't work with days ([dd]:hh:mm:ss). It's possible to display DD:HH:MM:SS that looks right but it would be a string and you wouldn't be able to make sensible calculations with it.
So in the attached I've split off whole (8 hour) days from the hours component into 2 columns. I've added a third column (Decimal8hrDays) which gives you whole days with the fraction of 8 hours as a decimal which agrees with your manual calculations. This you could sensibly do arithmetic on.

#### Attachments

• ExcelGuru11281Time Test for Help.xlsx
229.6 KB · Views: 4