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 code
(StartDate as date, EndDate as date) as number =>
let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)
in
CountDays
Thanks a lot!
(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, _)))
6 Replies
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, _)))(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, _)))