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

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

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.

2. How to use Duration function in PQ-->https://efficiency365.com/2018/08/31...s-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"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each #duration(0,0,0,[Service Target Runtime Seconds]))
in

3. 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.

4. 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?

5. No. I calculates Actual Days.

6. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•