Forum Discussion
Exclude Holiday in existing Networkday code
Hi all I have a code where I exclude the weekends to get the networkdays. Can someone help me to additional exclude the holidays which I want to set up in an excel file? This is my existing c...
(StartDate as date, EndDate as date) as number => List.Count(List.Select(List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _)))
let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)),
RemoveWeekendsHolidays = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _))
CountDays = List.Count(RemoveWeekends)
in
CountDays(StartDate as date, EndDate as date) as number =>
List.Count(List.Select(List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _)))
I don't really know how I should put this in my existing code... Could you write it with my existing code
(StartDate as date, EndDate as date) as number => List.Count(List.Select(List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _)))Thanks a lot! Any idea why the data load takes such a long time? is running for 10min and still didn't load the data...And I only have 3 dates to test in the holiday file.