Calculate Nth Business Day From Date in Power Query

You want to return the nth business day from a date in Power Query. And this offset should take into account holidays and weekends. It’s a common requirement in calendars or date related queries to retrieve a working day that is n number of days from your reference date.

So how can you retrieve it? In this earlier post I wrote how you can find the next working day using List.Generate. This post takes things a step further shows you how to find the nth business day in a month. That includes the options to look in the future or back in time.

Some time ago Imke wrote a way to retrieve the Nth Working Day using List functions. My article takes a different approach using List.Generate that achieves the same result.

Introduction

The solution in this post uses iteration with List.Generate. When looking for the nth business day, we have a starting date. And from that starting date we will increment or decrement with steps of 1 day until we reach the desired number of working days.

How could that work?

Retrieve Nth Weekday

Let’s say you want to find the 2nd business day after Thursday 15 September 2022. You want to count the number of weekdays after your date.

So first argument, with the initial value, starts with record that contains the fields:

  • Date, which is the date after the reference date: Date.AddDays( #date( 2022, 9, 15 ), 1 ).
  • WD_Counter starts at 0 and registers how many workdays our list contains.
  • IsWorkDay indicates if the current date value is a workday.

The next step is define when List.Generate has reached the nth business day and can stop generating values. This happens when we find the workday Counter to equal 2 and when IsWorkDay equals true.

If we don’t check whether the date is a WorkDay and the second working day falls on a Friday, the query would otherwise also return weekend days.

The third argument instructs List.Generate how to generate the next value for the record fields. In this case we want the:

  • Date to increase by 1 day
  • WD_Counter to increase only if the Date equals a Working Day
  • IsWorkDay to indicate true when the Date equals a working day and otherwise false.
List.Generate(
  () => [ Date = Date.AddDays( #date( 2022, 9, 15 ), 1 ), 
          WD_Counter = 0, 
          IsWorkday = null
        ], 
  each if [WD_Counter] = 2 and [IsWorkday] = true then false else true, 
  each  [
          Date       = Date.AddDays( [Date], 1 ), 
          WD_Counter = if Date.DayOfWeek( [Date], 1 ) < 5 
                          then [WD_Counter] + 1 else [WD_Counter], 
          IsWorkday  = Date.DayOfWeek( [Date], 1 ) < 5
        ]
)

So far this query returns a list with 4 records. The second weekday after Thursday 15 September 2022 is Monday 19 September 2022. The query returns records for 16 -19 September 2022.

Since we are only interested in the date of the 2nd weekday after our reference date, you can specify List.Generates optional 4th selector argument. This returns a list of only dates.

And when you wrap the list of dates in the List.Last function, it returns the latest date in the list which is our 2nd weekday.

List.Last(  
  List.Generate(
    () => [ Date = Date.AddDays( #date( 2022, 9, 15 ), 1 ), 
            WD_Counter = 0, 
            IsWorkday = null
          ], 
    each if [WD_Counter] = 2 and [IsWorkday] = true then false else true, 
    each  [
            Date       = Date.AddDays( [Date], 1 ), 
            WD_Counter = if Date.DayOfWeek( [Date], 1 ) < 5 
                            then [WD_Counter] + 1 else [WD_Counter], 
            IsWorkday  = Date.DayOfWeek( [Date], 1 ) < 5
          ],
    each [Date]
  )
)

This formula returns the 19 September 2022 which is the day we were looking for. In the UI these three steps looked like:

Nth Business Day in Power Query

Retrieve Nth Business Day

In case you want to return the nth business day your function should also respect holiday. Let’s imagine we continue our previous example and want to indicate 19 September 2022 is a holiday.

The only things we need to change are:

  • WD_Counter should not increment on a holiday
  • IsWorkday should indicate false on holidays
List.Last(  
  List.Generate(
    () => [ Date = Date.AddDays( #date( 2022, 9, 15 ), 1 ), 
            WD_Counter = 0, 
            IsWorkday = null
          ], 
    each if [WD_Counter] = 2 and [IsWorkday] = true then false else true, 
    each  [
            Date       = Date.AddDays( [Date], 1 ), 
            WD_Counter = if Date.DayOfWeek( [Date], 1 ) < 5 
                  and not List.Contains( {#date( 2022, 9, 19 )}, [Date] ) 
                            then [WD_Counter] + 1 else [WD_Counter], 
            IsWorkday  = Date.DayOfWeek( [Date], 1 ) < 5
                  and not List.Contains( {#date( 2022, 9, 19 )}, [Date] ) 
          ],
    each [Date]
  )
)

With this adjustment the code returns Tuesday 20 September 2022 as second business day after 15 September 2022.

For as we use the Holidays list multiple times now and we may want to reference a longer list of holidays, it’s good practice to turn this code into variables.

let
  Holidays = {#date( 2022, 9, 19 )},
  ListOfDates =
    List.Generate(
      () => [ Date = Date.AddDays( #date( 2022, 9, 15 ), 1 ), 
              WD_Counter = 0, 
              IsWorkday = null
            ], 
      each if [WD_Counter] = 2 and [IsWorkday] = true then false else true, 
      each  [
              Date       = Date.AddDays( [Date], 1 ), 
              WD_Counter = if Date.DayOfWeek( [Date], 1 ) < 5 
                              and not List.Contains( Holidays , [Date] ) 
                              then [WD_Counter] + 1 else [WD_Counter], 
              IsWorkday  = Date.DayOfWeek( [Date], 1 ) < 5
                              and not List.Contains( Holidays, [Date] ) 
            ],
      each [Date]
    ),
  Result =  List.Last(  ListOfDates )
in
  Result

Past and Future Business Days

In case you want to look into the past for the nth business day from the reference date, the code requires multiple adjustments.

To find the 5th working day before the reference date you would need to adjust:

  • Date field in argument 1 that defines the first date to check. It should look back so the second argument of Date.AddDays should be -1 instead of +1.
  • WD_Counter condition in argument 2 and change it to 5
  • Date field in argument 3 that defines the next date value. It should look back, just like in argument 1.

Instead of changing these variables manually, we could use some code that looks at the WorkingDay Offset. Let’s introduce some new variables:

  • WorkingDayOffset determines how many days you want to look backward or forward.
  • AddDays returns +1 for positive WorkingDayOffsets and -1 for negative offset values. We use this in the Date.AddDays function in line 8 and 15 to determine whether the next value should increment or decrement.
  • NumOfWDs refers to the number of working days we want to have in our list. Number.Abs makes sure this number is always positive.

Adjusting this in our code looks like:

let
  WorkDayOffset = -5,
  AddDays = Number.Sign( WorkDayOffset ),
  NumOfWD = Number.Abs( WorkDayOffset ),
  Holidays = {#date( 2022, 9, 19 )},
  ListOfDates =
    List.Generate(
      () => [ Date = Date.AddDays( #date( 2022, 9, 15 ), AddDays ), 
              WD_Counter = 0, 
              IsWorkday = null
            ], 
      each if [WD_Counter] = NumOfWD 
         and [IsWorkday] = true then false else true, 
      each  [
              Date       = Date.AddDays( [Date], AddDays ), 
              WD_Counter = if Date.DayOfWeek( [Date], 1 ) < 5 
                              and not List.Contains( Holidays , [Date] ) 
                              then [WD_Counter] + 1 else [WD_Counter], 
              IsWorkday  = Date.DayOfWeek( [Date], 1 ) < 5
                              and not List.Contains( Holidays, [Date] ) 
            ],
      each [Date]
    ),
  RelevantWorkDay=  List.Last(  ListOfDates )
in
  RelevantWorkDay

Final Adjustments

For most of your requirements, you are now good to go. Yet there are some slight adjustments to make the code more robust and more like the Excel WORKDAY function. We still need to make changes for the following consideration:

  • Users may or may not want to make use of Holidays. To give them the option to use or omit the holidays, we need to make sure our function can handle both scenario’s. Our code currently looks whether the generated Date value falls within a list of holidays. In case the user omits this argument, we can provide the List.Contains function with an empty list {}. List.Contains( {}, [Date] ) will always return false and therefore not impact the check of whether the Date falls on a holiday.
  • When used, the Holidays argument will be called multiple times. For performance reasons we will Buffer these values into memory using List.Buffer.
  • The 3rd argument checks whether the next value is a working day twice. Once for the WD_Counter and once for the IsWorkDay field. We will use a variable to evaluate this only once.
  • The Excel WORKDAY function returns the entered date when a user enter 0 as WorkDayOffset. We will add an argument that returns the reference date when a user enters an offset value of 0.
let
  WorkDayOffset = -5,
  AddDays = Number.Sign( WorkDayOffset ),
  NumOfWD = Number.Abs( WorkDayOffset ),
  Holidays = {#date( 2022, 9, 19 )},
  BufferedHolidays = List.Buffer( Holidays ?? {} ),
  ListOfDates =
    List.Generate(
      () => [ Date = Date.AddDays( #date( 2022, 9, 15 ), AddDays ), 
              WD_Counter = 0, 
              IsWorkday = null
            ], 
      each if [WD_Counter] = NumOfWD 
            and [IsWorkday] = true then false else true, 
      each  
          let
            NextDateIsWD = Date.DayOfWeek([Date], 1)
              < 5 and not List.Contains( BufferedHolidays, [Date])
          in
            [
              Date       = Date.AddDays( [Date], AddDays ), 
              WD_Counter = if NextDateIsWD then [WD_Counter] + 1 
                           else [WD_Counter], 
              IsWorkday  = NextDateIsWD 
            ],
      each [Date]
    ),
  RelevantWorkDay =  List.Last(  ListOfDates ),
  Result = if WorkDayOffset = 0 then #date( 2022, 9, 15 ) 
           else RelevantWorkDay
in
  Result

With these steps you have now seen all logic needed to prepare the final function to retrieve your nth business day.

Function Code for Nth Business Day

With some slight adjustments in the code, you arrive at the below function.

let func =  
( ReferenceDate as date, WorkdayOffset as number, optional Holidays as list) =>
let
  /* Debug parameters
       ReferenceDate = #date( 2021,1,1 ),
       WorkdayOffset = 1,                        
       Holidays = { #date( 2021,1,1 ) },
  */     

  // Returns empty list if Holidays are omitted
  BufferedHolidays = List.Buffer( Holidays ?? {} ),

  // Determines the positive or negative offset of Date.AddDays
  AddDays = Number.Sign( WorkdayOffset ),

  // Creates the List of Dates until it reaches the relevant Workday
  ListOfDates = 
      List.Generate(
        () => [
                Date       = Date.AddDays( ReferenceDate, AddDays ),
                WD_Counter = 0,
                IsWorkday  = null
              ],
        each if [WD_Counter] = Number.Abs( WorkdayOffset )
                  and [IsWorkday] = true then false else true,
        each
          let
            NextDateIsWD = Date.DayOfWeek([Date], 1)
              < 5 and not List.Contains( BufferedHolidays, [Date])
          in
            [
              Date       = Date.AddDays([Date], AddDays ),
              WD_Counter = if NextDateIsWD then [WD_Counter] + 1 
                              else [WD_Counter],
              IsWorkday  = NextDateIsWD
            ],
        each [Date]
      ),

  // Returns the relevant Workday, which is always last in the list
  RelevantWorkday = List.Last( ListOfDates ),

  // When 0 is used as Workdayoffset, the function returns the referencedate
    Result = if WorkdayOffset = 0 then ReferenceDate else RelevantWorkday
  in
    Result,
documentation = [
Documentation.Name =  " WORKDAY ",
Documentation.Description = " Function returns the date that lies an specfied number of business days from the reference date. ",
Documentation.LongDescription = " Function returns the date that lies an specfied number of business days from the reference date. The function takes into account weekends and can optionally respect a list of holiday dates.  ",
Documentation.Category = " Nth Business Day ",
Documentation.Source = " https://gorilla.bi – https://gorilla.bi/power-query/nth-business-day-from-date/ ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Rick de Groot",
Documentation.Examples = {[Description =  "  ",
Code = " let
       ReferenceDate = #date( 2022, 9, 15 ),
       WorkdayOffset = 2,                        
       Holidays = { #date( 2022, 9, 19 ) },
    Result = WORKDAY( ReferenceDate, WorkDayOffset, Holidays )
    
in
    Result ",
Result = " #date(2022, 9, 20) 
  "]}]
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
Function Documentation

The function has all the logic in place to find the nth business day. It works similar to the Excel WORKDAY function and with that the parameters behave as follows:

  • the Reference Date is the starting date from which the function will look for the nth business day. The nth business day can be either a positive or negative number.
  • the WorkdayOffset determines how many business days you want to look forward or backward. Inputting 5 means returning the date that is 5 working days in the future and -5 would return the date that is 5 business days back in time.
  • The Holidays argument allows you to input a list of dates. The easiest way to maintain this, is to add a separate query that holds your dates. You can then reference that date column in this query by writing: TableName[ColumnName]. That is enough for the function to respect those days.

In your Calendar you can then see your nth business day function in action:

WORKDAY Function in Power Query Calendar

You can now easily find the metrics like:

// Formula retrieves the 3rd business day from the date
= WORKDAY( [Date], 3)

// Formula retrieves the 3rd business day of the month the current row's date is in
= WORKDAY( Date.AddDays( Date.StartOfMonth( [Date], -1 ) ), 3, Holidays )

// Retrieves the day that is 3 working days after the first of the month
= WORKDAY( Date.StartOfMonth( [Date]), 3, Holidays )

// nth business day of Week
= WORKDAY( Date.StartOfWeek( [Date] ) , 3, Holidays )


You can download the accompanying file here:

If this all went a bit too quick, I recommend delving into one of my earlier List.Generate posts:

That’s all for now, enjoy Power Query!

Recommend Reading

>>> Power Query - Foundations <<<List Functions (200+ examples)Text functions (150+ examples)Creating Tables from Scratch (40+ examples)Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

Leave a comment