Forum Discussion
Helper IIAdding working days to a date excluding weekends and holidays
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
Helper IIHi,
US Holidays.
New Years , Memorial Day, Independance Day, Labor Day, Thanksgiving, Christmas Day
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
- Bryanna
Awesome! Thank you so much!
- lbendlin
Super User
These dates never change. There is no point doing this in Power Query or DAX. Use a precompiled external reference table.
This was very helpful, thanks! What if I want to keep a datetime the same if before 2PM, but after 2PM, it adds one day, excluding weekends and holidays?
In shorter words, how would I implement a 2pm cutoff time in this query?
- dufoq3
Hi, create new topic and provide sample data as table + extected result based on sample data.
- lbendlin
Add 10/24 to your datetime value.
I have this query, but it is not working for me. It will work for time after 2PM-- adding one day. But will not work to pull the same day if before 2PM.
let WorkDayAddTime = (Start as datetime) as datetime => let Holidays = List.Buffer(#"Observed Holidays"[Date]), Time = Time.From(Start), IsAfter2PM = Time >= #time(14, 0, 0), WorkDaysToAdd = if IsAfter2PM then 1 else 0, Source = List.Generate( () => Date.AddDays(Start, WorkDaysToAdd * 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", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sort Table", "Index", 1, 1, Int64.Type), #"Filtered Rows 1" = Table.SelectRows(#"Added Index", each [Index] = WorkDaysToAdd), #"Select Column" = Table.SelectColumns(#"Filtered Rows 1", "Column1") in Record.Field(Table.Max(#"Select Column", "Column1"), "Column1") in WorkDayAddTime
