Forum Discussion
NETWORKDAYS in Hours
Hi,
I would like to calculate NETWORKDAYS between 2 date/times in hours (not days) in power query (not DAX), excluding weekeneds. Ideally, exalso excluding specific dates from another table (Bank Holidays).
Is there any way to do that? Thanks in advance! 🙂
Hi Anonymous ,
According to your description, here's my solution. Copy-paste the below code to a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM31DcyMDJWMLCwMjAAIiUdhJihEVQsVgeXSiOsKo2xqDQl2kwLdJWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Received" = _t, #"Order Delivered" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Received", type datetime}, {"Order Delivered", type datetime}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each {Number.From(DateTime.Date([Order Received]))+1..Number.From(DateTime.Date([Order Delivered]))-1}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type date}}), #"Transform"=Table.ReplaceValue(#"Changed Type1",each[Custom.1],each try if List.Contains(#"Date"[Date],[Custom.1]) then [Custom.1]else if Date.DayOfWeek([Custom.1],Day.Monday)<5 then null else[Custom.1]otherwise null,Replacer.ReplaceValue,{"Custom.1"}), #"Grouped Rows" = Table.Group(Transform, {"Order Received", "Order Delivered"}, {{"Count", each List.NonNullCount(_[Custom.1]), Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hours", each Duration.TotalHours((if Date.DayOfWeek([Order Delivered],Day.Monday)>=5then DateTime.From(Date.AddDays(DateTime.Date([Order Delivered]),1)) else [Order Delivered])-(if Date.DayOfWeek([Order Received],Day.Monday)>=5then DateTime.From(Date.AddDays(DateTime.Date([Order Received]),1)) else [Order Received]))-[Count]*24) in #"Added Custom"Date table is a list of dates:
Get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Can you share sample input and Expected output?
Please find an example below (Dates in D/M/YYYY format):
Case 1:
Order Received: 1/6/2023 08:00:00
Order Delivered: 1/6/2023 12:00:00
Delivery Duration (h): 4
Explanation:
Days excluding weekends: 1/6-1/6 = 0 (no weekend)
Hours: 12-8 = 4; Minutes: 0-0 = 0
Duration (h): 0*24+4 = 4Case 2:
Order Received: 1/6/2023 08:00:00
Order Delivered: 2/6/2023 12:00:00
Delivery Duration (h): 28
Explanation:
Days excluding weekends: 2/6-1/6 = 1 (no weekend)
Hours: 12-8 = 4; Minutes: 0-0 = 0
Duration (h): 1*24+4 = 28Case 3:
Order Received: 1/6/2023 08:00:00
Order Delivered: 5/6/2023 12:00:00
Delivery Duration (h): 52
Explanation:
Days excluding weekends: 5/6-1/6 - 2 (Sat/Sun) = 2 (3/6/2023 Saturday, 4/6/23023: Sunday)
Hours: 12-8 = 4; Minutes: 0-0 = 0
Duration (h): 2*24+4 = 52Bank Holidays can be in another tables with those dates being excluded from the calculation (like Saturdays/Sundays).
Hi Anonymous ,
According to your description, here's my solution. Copy-paste the below code to a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM31DcyMDJWMLCwMjAAIiUdhJihEVQsVgeXSiOsKo2xqDQl2kwLdJWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Received" = _t, #"Order Delivered" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Received", type datetime}, {"Order Delivered", type datetime}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each {Number.From(DateTime.Date([Order Received]))+1..Number.From(DateTime.Date([Order Delivered]))-1}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type date}}), #"Transform"=Table.ReplaceValue(#"Changed Type1",each[Custom.1],each try if List.Contains(#"Date"[Date],[Custom.1]) then [Custom.1]else if Date.DayOfWeek([Custom.1],Day.Monday)<5 then null else[Custom.1]otherwise null,Replacer.ReplaceValue,{"Custom.1"}), #"Grouped Rows" = Table.Group(Transform, {"Order Received", "Order Delivered"}, {{"Count", each List.NonNullCount(_[Custom.1]), Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hours", each Duration.TotalHours((if Date.DayOfWeek([Order Delivered],Day.Monday)>=5then DateTime.From(Date.AddDays(DateTime.Date([Order Delivered]),1)) else [Order Delivered])-(if Date.DayOfWeek([Order Received],Day.Monday)>=5then DateTime.From(Date.AddDays(DateTime.Date([Order Received]),1)) else [Order Received]))-[Count]*24) in #"Added Custom"Date table is a list of dates:
Get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello v-yanjiang-msft,
Thank you for your post. I was looking for a solution to calculate network days in hours and your post great helped. Thanks! However, I have to say there is bug in the calculation. The calcuation when the second date is a weekend day is incorrect. See below I added two records based on your data. I believe removing the Data.AddDays for the second day should be fixing this bug.Also I see you added a Date table which I believe this is for holidays, but in the later calculation, you didn't consider if the first day or the second day in a day in the holiday. Do you have an updated version and include this part. 😉Thanks again.
