Forum Discussion
Bryanna
Helper II
Helper IIAdding working days to a date excluding weekends and holidays
Hi!
In another forum i found the below custom function to remove weekends when adding days to a date. I also need to exclude holidays. Any idea on how to exclude holidays from the below as well? Thanks!
let WorkDayAdd = (Start as date, WorkDays as number) as date =>
let
Source = List.Generate( () => Date.AddDays(Start, WorkDays * 4), each _ >= Date.AddDays(Start,1), each Date.AddDays(_, -1 )),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "WeekDay", each Date.DayOfWeek([Column1],Day.Monday)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each [WeekDay] < 5),
#"Sort Table" = Table.Sort(#"Filtered Rows", {"Column1"}),
#"Added Index" = Table.AddIndexColumn(#"Sort Table", "Index", 1, 1, Int64.Type),
#"Filtered Rows 1" = Table.SelectRows(#"Added Index", each [Index] = WorkDays),
#"Select Column" = Table.SelectColumns(#"Filtered Rows 1", "Column1")
in
Record.Field(Table.Max(#"Select Column", "Column1"), "Column1")
in
WorkDayAdd
try this, but edit step Holidays in Advanced Editor and replace US_Holidays[Date] with your holidays Table[Column] reference, i.e. if you have table called Hol and in this table column date so you should replace it with Hol[date]
let WorkDayAdd = (Start as date, WorkDays as number) as date => let Holidays = List.Buffer(US_Holidays[Date]), Source = List.Generate( () => Date.AddDays(Start, WorkDays * 4), each _ >= Date.AddDays(Start,1), each Date.AddDays(_, -1 )), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "WeekDay", each Date.DayOfWeek([Column1],Day.Monday)), #"Added Custom1 Holiday" = Table.AddColumn(#"Added Custom", "Holiday", each List.Contains(Holidays, [Column1]), type logical), #"Filtered Rows" = Table.SelectRows(#"Added Custom1 Holiday", each [WeekDay] < 5 and [Holiday] = false), #"Sort Table" = Table.Sort(#"Filtered Rows", {"Column1"}), #"Added Index" = Table.AddIndexColumn(#"Sort Table", "Index", 1, 1, Int64.Type), #"Filtered Rows 1" = Table.SelectRows(#"Added Index", each [Index] = WorkDays), #"Select Column" = Table.SelectColumns(#"Filtered Rows 1", "Column1") in Record.Field(Table.Max(#"Select Column", "Column1"), "Column1") in WorkDayAdd
9 Replies
BryannaHi,
US Holidays.
New Years , Memorial Day, Independance Day, Labor Day, Thanksgiving, Christmas Day
- dufoq3
Community Champion
try this, but edit step Holidays in Advanced Editor and replace US_Holidays[Date] with your holidays Table[Column] reference, i.e. if you have table called Hol and in this table column date so you should replace it with Hol[date]
let WorkDayAdd = (Start as date, WorkDays as number) as date => let Holidays = List.Buffer(US_Holidays[Date]), Source = List.Generate( () => Date.AddDays(Start, WorkDays * 4), each _ >= Date.AddDays(Start,1), each Date.AddDays(_, -1 )), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "WeekDay", each Date.DayOfWeek([Column1],Day.Monday)), #"Added Custom1 Holiday" = Table.AddColumn(#"Added Custom", "Holiday", each List.Contains(Holidays, [Column1]), type logical), #"Filtered Rows" = Table.SelectRows(#"Added Custom1 Holiday", each [WeekDay] < 5 and [Holiday] = false), #"Sort Table" = Table.Sort(#"Filtered Rows", {"Column1"}), #"Added Index" = Table.AddIndexColumn(#"Sort Table", "Index", 1, 1, Int64.Type), #"Filtered Rows 1" = Table.SelectRows(#"Added Index", each [Index] = WorkDays), #"Select Column" = Table.SelectColumns(#"Filtered Rows 1", "Column1") in Record.Field(Table.Max(#"Select Column", "Column1"), "Column1") in WorkDayAdd