Return Next Working Day in Power Query

You may want to use Power Query to return the next working day in your dataset. That working day should naturally not be in the weekend and respect a list of holiday dates relevant to your analysis.

Unfortunately, there is no build in function to do that. Excel has the WORKDAY function, in DAX you can juggle with NETWORKDAYS and create something, but Power Query has neither.

So how can you calculate this? If you’re only looking for the next working day, this post has got you covered. For the nth working day this function can help.

Let’s first look at how to find the next week day. From there we will build on to find the next work day.

Next Week Day with List.Generate

One way to approach this is by using List.Generate. If you are new to this function, this post gets you started with List.Generate.

Let’s delve into what kind of logic we would need. The current row in your table has a date. To find the next working day you need to how many consecutive days fall in:

  1. the weekend.
  2. on a holiday.

When you know the number of consecutive days that are not working days, you can use the Date.AddDays function, to offset the current date to the first working day.

Let’s first pretend there are no holidays involved. You want to create a list of consecutive days that fall in the weekend. The first day we check is the day after the current row. Therefore the starting value of the list is the current date + 1.

If this day is a weekend day you check if the following day also belongs to the weekend, but if it falls on a weekday, the list generation should stop.

  List.Generate(
   () => Date.AddDays( [Date], 1 ),         // start value is date + 1
   each Date.DayOfWeek(_, Day.Monday) >= 5, // if date falls in weekend
   each Date.AddDays(_, 1)                  // generate date of the next day
)
Formula for number of weekend days

This formula always returns the numbers of weekend days that come after the current row. To check this, you can create a column that counts number of values in the list you created by using List.Count.

Number of following Weekend Days

With this in place you can easily create a formula to find the next weekday. To return the next day you could can add a column with the Date.AddDays formula:

= Date.AddDays( 
   [Date], // take the current date
   1 )     // and add 1 day to it

And because you have the code available that counts the number of weekend days that follow, you can now adjust the formula to:

= Date.AddDays( 
  [Date],              // increase the current date
  1                    // by one day 
   + List.Count(       // + the number of
      List.Generate(   // weekenddays
         () => Date.AddDays( [Date], 1 ),
         each Date.DayOfWeek(_, Day.Monday) >= 5,
         each Date.AddDays(_, 1) 
      )
    )
)
Next Working Day with List.Generate

So far we have looked at how to find the next weekday. Let’s now include the logic to take into account holidays.

Next Working Day with List.Generate

The next working day from a specific day respects both the weekend and holidays. Since holidays can differ depending on your country and purpose it’s easiest if you create a table that contains the holidays.

For this example I created a table called Holidays that contains a Date column with all dates considered a holiday. The first step is to count the number of following days that fall in the weekend or on a holiday.

To do this we need to add an additional variable which is the list of dates that concern holidays. In List.Generate you can add multiple variables by using a record. If you’re new to this, check out this post that explains different List.Generate examples.

With this list of dates, we can then create an additional condition in the condition argument, which is the second one. This condition checks whether the next date is a weekend day or part of the list of holidays. Only then a value is returned.

List.Generate(
  () => [ x = Date.AddDays( [Date], 1 ), 
          y = Table.Column( Holidays, "Date" ) ], 
  each Date.DayOfWeek( [x], Day.Monday ) >= 5 
        or List.Contains( [y], [x] ), 
  each [x = Date.AddDays( [x], 1), y = [y] ]
)

Let’s say the January 6th and 7th of 2020 were holidays. Our table would then look like:

Number of NonWorking Days

When you know the number of non-working days that follow the current day, you can use this offset to find the next working day. By default your Date.AddDays function would add 1 day. On top of that you need the offset to end up at the next working day.

Your final formula then is:

= Date.AddDays(
    [Date], 
    1
      + List.Count(
          List.Generate(
            () => [ x = Date.AddDays( [Date], 1 ), 
                    y = Table.Column( Holidays, "Date" ) ], 
            each Date.DayOfWeek( [x], Day.Monday ) >= 5 
                  or List.Contains( [y], [x] ), 
            each [x = Date.AddDays( [x], 1), y = [y] ]
          )
        )
)
Next Working Day Date

To make your life easier, you can turn the logic into a function by pasting the following into the advanced editor:

(_Date as date, HolidayTable as table, HolidayColumnName as text) =>
Date.AddDays(
  _Date, 
  1
    + List.Count(
        List.Generate(
            () => [ x = Date.AddDays( _Date, 1 ), 
                    y = Table.Column( HolidayTable, HolidayColumnName ) ], 
             each Date.DayOfWeek( [x], Day.Monday >= 5 ) 
                or List.Contains( [y], [x] ), 
             each [x = Date.AddDays( [x], 1), y = [y] ]
        )
      )
)

The above function works well if you are only looking for the next working day in your dataset. If you need more flexibility to look at the nth working day before or after your date, this article shows how to calculate the nth business day in Power Query.

It’s a more advanced function that combines other techniques to return the desired date.

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.

1 thought on “Return Next Working Day in Power Query”

  1. Hi Rick,
    I am trying to use the function that you have provided to generate the next working day. When I enter the required details, I am getting the following error message:
    An error occurred in the ‘’ query. Expression.Error: We cannot convert the value false to type Number.
    Details:
    Value=FALSE
    Type=[Type]
    Is this something you have seen before? Potentially it could be the way I have this information saved separate table? (I have formatted this as a date and as text) Should I save my dates differently using a list or perhaps something that has not occurred to me?
    Many Thanks,

    Reply

Leave a comment