Adding working days to a date excluding weekends and holidays

[Bryanna]

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

[dufoq3]

Hi @Bryanna, do you have a list of your country holiday days? Or could you send me a link to a webpage with your contry holidays?

[Bryanna]

Hi,

US Holidays.

New Years , Memorial Day, Independance Day, Labor Day, Thanksgiving, Christmas Day

[dufoq3 · Accepted solution]

@Bryanna,

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

[ttaylor981]

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?

[lbendlin]

Add 10/24 to your datetime value.

[ttaylor981]

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

[dufoq3]

Hi, create new topic and provide sample data as table + extected result based on sample data.

[Bryanna]

Awesome! Thank you so much!

[lbendlin]

These dates never change. There is no point doing this in Power Query or DAX.  Use a precompiled external reference table.