Working Days between Dates in Power Query

A common requirement is to calculate the number of working days between dates in Power Query. That means count the number of days and subtract both weekend and holiday dates.

And whereas Excel has such a function, in Power Query you are left to create one yourself. Imke did something like that here. I wanted the Working Days function to work with negative increments and show you a few examples on how to use it. Therefore this post shows an adjusted approach.

So how can you create your own function?

Working Days logic

To create a NetWorkDays function in Power Query you will List functions to work. To improve your Power Query skills, I always advice people to master List Functions. They are versatile and you can combine them for powerful analysis.

Let’s say you want to find the number of working days between 1 October 2022 and 31 December 2022. And 26 December 2022 is considered a holiday.

The necessary steps to arrive there are:

  1. Compute the workings days from starting date up to and including ending date.
  2. Remove weekend dates
  3. Optionally remove the provided holiday dates
  4. Count the number of dates left

You can go ahead and use something like:

let
  // Define start and ending dates
  DateStart = #date( 2022, 10, 1 ), 
  DateEnd =   #date( 2022, 12, 31 ), 

  // Find the number of days between Start and End Date
  DaysBetween = Duration.Days( DateEnd - DateStart ), 

  // The number of days should create a series that begins at the start 
  // date and ends at the ending date. You need an additional day here.
  NumberOfDays = DaysBetween + ( if DateEnd < DateStart then - 1 else + 1 ),

  // Direction indicates date series direction (positive or negative)
  Direction = Number.Sign( NumberOfDays ), 

  // Creates dates between Start and End Date. Series can increment
  // or decrement with each step, decided by the Direction variable.
  ListOfDates = 
    List.Dates(
      DateStart, 
      Number.Abs( NumberOfDays ), 
      #duration( Direction, 0, 0, 0 ) // positive or negative steps
    ), 

  ListOfWeekDays = 
    List.Select(
      ListOfDates,  // exclude weekends                         
      each Date.DayOfWeek( _, Day.Monday ) + 1 <= 5
  ), 
  // List of holidays to exclude
  Holidays = { #date(2022, 12, 26) }, 

  // Selects only the non-holiday dates.
  ListOfWorkDays = 
    List.Difference(
      ListOfWeekDays, 
      Holidays ?? {} // Remove Holidays. When blank,  remove nothing
    ), 

  // Counts the number of working days
  NumberOfWorkDays = List.Count( ListOfWorkDays ), 

  // Multiplies the number of working days by 1 or -1, depending on 
  // whether starting date is before or after ending date.
  Positive_or_negative_WorkingDays = NumberOfWorkDays * Direction

in
  Positive_or_negative_WorkingDays

Before script is the elaborate version that works well for 2 tangible dates. To make things more flexible you may want to include some error checking. The final script has some slight adjustments.

The first line specifies the parameters for the function. This allows us to easily call the workday logic as a custom function.

The next difference is, when a user does not fill in the starting date or the ending date, the function will use 1 January 1900 as default. We use the COALESCE operator in M to achieve that.

Similarly, when a list of holidays is left empty, below function uses an empty list. Also here the COALESCE operator comes to the resue.

The final working days code for the function is:

let func = 
  (StartDate, EndDate, optional Holidays as list ) =>
let
    // When a date is null value, start calculating from 1 Jan 1900
    DateStart =         StartDate ?? #date( 1900, 1, 1 ), 
    DateEnd =           EndDate   ?? #date( 1900, 1, 1 ),

    // Find the number of days between Start and End Date
    DaysBetween =    Duration.Days( DateEnd - DateStart  ) ,

    // The correct date series requires the number of days FROM start to end,
    //  not the number of days BETWEEN start and end.
    NumberOfDays = DaysBetween + (if DateEnd < DateStart then -1 else + 1 ),

    // Direction indicates date series direction (positive or negative)
    Direction  =        Number.Sign( NumberOfDays ),

    Result =
      List.Count(                     
        List.Difference(              
          List.Select(                 
            List.Dates(
              DateStart, 
              Number.Abs( NumberOfDays ), 
              #duration( Direction, 0, 0, 0) // positive or negative steps
            ), 
            each Date.DayOfWeek( _, Day.Monday ) + 1 <= 5 // excl weekends
          ), 
          Holidays ?? {}  // exclude holidays. If omitted, use empty list
        )  
      ) 
      * Direction         // Show number of days positive or negatively
in Result,
documentation = [
Documentation.Name =  " fxNetWorkDays ",
Documentation.Description = " Adds a running total column to a table, based on a value column and one or more group by columns.",
Documentation.LongDescription = " This function computes the number of working days between two dates. It takes into accounts weekends and optionally a list of holidays. Leaving out the starting date or ending date results in the function using 1 January 1900 for calculating the number of working days. ",
Documentation.Category = " Net Work Days ",
Documentation.Source = " BI Gorilla – https://gorilla.bi ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Rick de Groot ",
Documentation.Examples = {[Description =  "  ",
Code = " let
    StartDate = #date( 2022, 10, 1 ),
    EndDate   = #date( 2022, 12, 31 ),
    ValuHolidays = #date( 2022, 12, 26 ),
    Result = fxRunningTotalByCategory( StartDate, EndDate, Holidays )
     
in
    Result ",
Result = " 60
  "]}]
 in 
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation) )

Working Days Code Examples

So how can you best make use of this function?

Workday Number of Week/Month/Year

This function can be useful in creating calendars. For example, you may want to add a field that shows the workday number of the week.

If you first add a column with the start of the week date, you can then reference it and return the workday number of the week:

= Date.StartOfWeek( [Date] )
// Add Start of Week date

= fxNetWorkDay( [Start of Week], [Date], { #date( 2022, 12, 26 ) } )
// Returns a column with the Workday number of the week
Working Days in Week

Similarly you can add a Start of the Month or Start of the Year Date. With those you can then compute the workday number of the month or year.

= Date.StartOfMonth( [Date] )
// Add Start of Month date

= fxNetWorkDay( [Start of Month], [Date], { #date( 2022, 12, 26 ) } )
// Returns a column with the Workday number of the month.

= Date.StartOfYear( [Date] )
// Add Start of Year date

= fxNetWorkDay( [Start of Year], [Date], { #date( 2022, 12, 26 ) } )
// Returns a column with the Workday number of the year.
Workday Number of the Month

Add Holiday Dates

In some net working days calculations it’s important to take into account a list of holidays. You can easily set up this function to take into account your desired list of holidays.

There are several ways to do this.

Hardcoding Dates

If you have a short list of holidays, you can hardcode those into your formula.

= fxNetWorkDay( 
  [StartDate], 
  [EndDate], 
  { #date( 2022, 12, 26 ) } 
)
// Counts the number of days between the starting and ending date
// taking into account 26 December 2022 as holiday

= fxNetWorkDay( 
  [StartDate], 
  [EndDate], 
  { #date( 2022, 12, 26 ), #date( 2022, 12, 27 ), #date( 2022, 12, 28 ) } 
)
// Counts the number of days between the starting and ending date
// taking into account 26, 27 and 28 December 2022 as holiday

This method is only convenient with a short number of dates. For most situation you will want to reference a table with holidays, or a list with holidays.

Holidays Table

In most situation a holidays table is convenient. If you have a holidays table that’s called Holidays and it includes a column with the name Date, you can reference your holidays by writing:

= fxNetWorkDay( 
  [StartDate], 
  [EndDate], 
  Holidays[Date]   
)
// Respects holidays from the 'Date' column in the 'Holidays' table

Holidays List

In other cases you may have a list of holidays. Either in the same query, or perhaps you used the Power Query ‘drill-down’ functionality to zoom in on a table column with holidays. Either way, you can easily reference your list of holidays by providing the Query-name with the holidays-list, or the step-name with the holidays-list.

= fxNetWorkDay( 
  [StartDate], 
  [EndDate], 
  HolidaysList // Can be a separate query or a an earlier step name.
)
// Respects Holidays from the HolidaysList

Hopefully that gave you some ideas on how this function works. By combining list functions, including error checking and turning logic into a function, you can easily find the number of working days between two days.

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