Add 10 Business Days to Date Excluding Holidays

[Txtcher]

I am working in Power Query only and need an M-code function (not a DAX solution).

I need to add 10 business days (no Sundays or Saturdays) to an Assigned Date, excluding holidays, to come up with a Due Date. For example, if the Assigned Date is 1/19/2025, the Due Date would be: 2/3/2025.

I have a holiday table with a date column: Fed_Holidays.[Date]

I have searched for hours to find a solution and can only find solutions to calculate net working days between 2 dates which is not what I need. Unfortunately, I am too inexperienced to convert those solutions into the solution I need, therefore I need your help.

Thank you in advance.

[ronrsnfld · Accepted solution]

Unless there are some holidays in late January, I don't understand how you get 2/3/2025 by adding 10 business days to 1/19/2025.

I see the following:

If that analysis is correct, then you can use this Power Query custom function to add Workdays:

(startDt as date, Count as number, Holidays as list)=>

[a=List.Generate(
    ()=>[d=Date.AddDays(startDt,Number.Sign(Count)), 
         idx=if Date.DayOfWeek(d,Day.Monday)>=5 or List.Contains(Holidays,d) then 0 else 1],
    each [idx] <= Number.Abs(Count),
    each [d=Date.AddDays([d],Number.Sign(Count)), 
         idx=if Date.DayOfWeek(d,Day.Monday)>=5 or List.Contains(Holidays,d) then [idx] else [idx] + 1],
    each [d]),
 b=List.Select(a, each Date.DayOfWeek(_,Day.Monday)<5 and not List.Contains(Holidays,_)),
 c=List.Last(b)][c]

[ronrsnfld · Accepted solution]

Unless there are some holidays in late January, I don't understand how you get 2/3/2025 by adding 10 business days to 1/19/2025.

I see the following:

If that analysis is correct, then you can use this Power Query custom function to add Workdays:

(startDt as date, Count as number, Holidays as list)=>

[a=List.Generate(
    ()=>[d=Date.AddDays(startDt,Number.Sign(Count)), 
         idx=if Date.DayOfWeek(d,Day.Monday)>=5 or List.Contains(Holidays,d) then 0 else 1],
    each [idx] <= Number.Abs(Count),
    each [d=Date.AddDays([d],Number.Sign(Count)), 
         idx=if Date.DayOfWeek(d,Day.Monday)>=5 or List.Contains(Holidays,d) then [idx] else [idx] + 1],
    each [d]),
 b=List.Select(a, each Date.DayOfWeek(_,Day.Monday)<5 and not List.Contains(Holidays,_)),
 c=List.Last(b)][c]

[Txtcher]

Hello,

January 20 is a federal holiday in the US, so that makes the date February 3, because the 1st and the 2nd are Sat & Sun.

[ronrsnfld]

In that case, the function will work as is. Did you try it?

[Txtcher]

Yes, thank you. I did try it. And it works perfectly. However, I am not comfortable using something I don't understand. I know it is a big ask, but can you step me through it?

Again, thank you.

[ronrsnfld · Accepted solution]

The input arguments are obvious by their names.

You will need to familiarize yourself with the various functions used in the custom function.

We use the Date.Add function embedded in the List.Generate function to create a list of dates.

But we only increment the Counter (idx) if the generated date is not a holiday or weekend.

Because the Counter is not being incremented on non-business days, there is a chance that the final dates might occur on a non-business day, so we need to remove non-business days from the final list.

We then return the last date in the list.

[Txtcher]

Okay now the lights are flickering. I understood some of the functions, but missed the date functions were in the context of List.Generate (newbie using List functions).

This part confuses me: Number.Sign(Count)*1

The function returns a 1 for a positive number or -1 for a negative number: Number.Sign(10) returns 1. But why multiply it by 1?

Thank you for your time and generosity. 

[ronrsnfld]

Nice catch. The *1 is unnecessary. I Put it in during troubleshooting and forgot to remove it.