Need to calculate Networkdays from datetime stamp

Hi Anonymous ,

Given both "Opened Date" and "Closed Date" always fall on Working days and you have a list of Holidays like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSNzDUNzIwMlQwtLAyNLUyNlbSUTIyRRY1MLEyMVGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opened date" = _t, #"Closed date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opened date", type datetime}, {"Closed date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
        ListOfDates = List.Dates(Date.From([Opened date]),Number.From(Date.From([Closed date])-Date.From([Opened date])),#duration(1,0,0,0)),
        ListOfHolidays = List.Intersect({Holidays,ListOfDates}),
        ListOfWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_,1) > 4 ),
        ListOfDatestoSubstract = List.Distinct(List.Combine({ListOfHolidays,ListOfWeekends})),
        DaystoSubstract = List.Count(ListOfDatestoSubstract)
        in
        Duration.TotalDays([Closed date]-[Opened date]) - DaystoSubstract)
in
    #"Added Custom"

 

Hi Anonymous ,

Ok. You can use Bill Szysz's Function fxSOWH (Sum of working hours) for your purposes.
More details here

Link to the original Excel file

As you tell me, for now, don't care about the Start and End of working hours I set it as below:

Then just changed the Sum of working hours format from Hour to General and got what you are looking for: