Find Overlap Days between two date ranges

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.