Calculate Nth Business Day From Date in Power Query

In this article, we’re going to show you step by step how to calculate the nth business day from a date in Power Query. We’ll make use of an offset that can also take into account holidays and weekend days.

This article is part of the Date Table series, which consists of:

Here’s a quick overview of the topics covered in this article:

Table of contents

• Introduction
• Retrieve Nth Weekday
• Retrieve Nth Business Day
• Past and Future Business Days
• Final Adjustments
• Function Code for Nth Business Day
• Conclusion

Introduction

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 an earlier post, I wrote how you can find the next working day using List.Generate. This post takes things a step further and shows you how to find the nth business day in a month. That includes the option 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.

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, the first argument, with the initial value, starts with a 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 to 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.Generate‘s optional 4th selector argument. This returns a list of only dates.

And when you wrap the list of dates in the List.Last function, 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 this:

Retrieve Nth Business Day

In case you want to return on the Nth business day, your function should also respect holidays. Let’s imagine we continue our previous example and want to indicate that 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 the second business day after 15 September 2022.

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 the following:

• The date field in argument 1 defines the first date to check. It should look back to the second argument of Date.AddDays should be -1 instead of +1.
• WD_Counter condition in argument 2 and change it to 5
• The date field in argument 3 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 backwards or forward.
• AddDays returns +1 for positive WorkingDayOffsets and -1 for negative offset values. We use this in the Date.AddDays function in lines 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 on our list. Number.Abs makes sure this number is always positive.

Adjusting this in our code looks like this:

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 scenarios. Our code currently looks at 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 enters 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 the 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))

The function has all the logic in place to find the Nth business day. It works similarly 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 backwards. 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:

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:

Conclusion

And that’s it! Now you know how to calculate the nth business day from a specific date in Power Query. If this all went a bit too quickly, I recommend delving into one of my earlier List.Generate posts:

• List.Generate in Power Query: Tutorial with Easy Examples
• Running Total in Power Query (Ultimate Guide)
• Compute a Running Total by Category in Power Query
• How to use List.Generate to make API Calls in Power Query M

That’s all for now. Happy querying!