Forum Discussion
Find Overlap Days between two date ranges
hi team , please help me kindly with this challenging situation. I have a table with employee allocation details , each employee can have more than one tasks allocated to him with start and end date .
Eg : Employee 1
task 1 : June 1st to June 20th
task 2 : May 30th to June 16th .
I need to calculate the number of working days ( Exclude sat & sun ) for this employee with task overlap dates should be distinct . like june 1st is included in both date ranges so should be considered as one only not 2 days .
can u help me with this scenario
Hi
List.Count(
List.Select(
List.Distinct(
List.Dates([Start 1],Duration.Days([End 1]-[Start 1])+1,#duration(1,0,0,0))
& List.Dates([Start 2],Duration.Days([End 2]-[Start 2])+1,#duration(1,0,0,0))),
each Date.DayOfWeek(_,Day.Monday)<5)
)
)Stéphane
Hi Anonymous
You can add a custom column with slorin 's code in Power Query Editor. It should work if all start dates and end dates are on the same row for an employee.
If every task has a single row like below,
You can try this solution. Create a new query with below code to see how it works.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lFKBGIjAyNjBa/SvFRktoKRgVKsDlRVEkzGN7FSwdgARZ2hGUSdEVA0GaGuKDlDwQSVa2iAUJmCX6WFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Task = _t, #"Start date" = _t, #"End date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Task", type text}, {"Start date", type date}, {"End date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Alldata", each _, type table [Employee=nullable text, Task=nullable text, Start date=nullable date, End date=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Count(List.Select(List.Distinct(List.Combine(List.Transform(Table.ToRecords([Alldata]), each List.Dates(_[Start date], Duration.Days(_[End date] - _[Start date])+1, #duration(1,0,0,0))))), each Date.DayOfWeek(_,Day.Monday)<5))) in #"Added Custom"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
3 Replies
Hi
List.Count(
List.Select(
List.Distinct(
List.Dates([Start 1],Duration.Days([End 1]-[Start 1])+1,#duration(1,0,0,0))
& List.Dates([Start 2],Duration.Days([End 2]-[Start 2])+1,#duration(1,0,0,0))),
each Date.DayOfWeek(_,Day.Monday)<5)
)
)Stéphane
Hi Anonymous
You can add a custom column with slorin 's code in Power Query Editor. It should work if all start dates and end dates are on the same row for an employee.
If every task has a single row like below,
You can try this solution. Create a new query with below code to see how it works.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lFKBGIjAyNjBa/SvFRktoKRgVKsDlRVEkzGN7FSwdgARZ2hGUSdEVA0GaGuKDlDwQSVa2iAUJmCX6WFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Task = _t, #"Start date" = _t, #"End date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Task", type text}, {"Start date", type date}, {"End date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Alldata", each _, type table [Employee=nullable text, Task=nullable text, Start date=nullable date, End date=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Count(List.Select(List.Distinct(List.Combine(List.Transform(Table.ToRecords([Alldata]), each List.Dates(_[Start date], Duration.Days(_[End date] - _[Start date])+1, #duration(1,0,0,0))))), each Date.DayOfWeek(_,Day.Monday)<5))) in #"Added Custom"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.