Working Days between Dates in Power Query (DATEDIFF)

Do you need to figure out how many working days are there between two dates in Power Query? In this article, I’m going to show you how to calculate the difference between two dates. I’ll explain how you can use Power Query to count the days while excluding weekends and holidays.

And whereas Excel and SQL have a function for it, the M Language does not. So, this article helps in creating the DATEDIFF function in Power Query. Imke did something like that here, but I wanted the Working Days function to work with negative increments. Therefore, this post shows an adjusted approach.

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

By the end of this article, you’ll be able to find out how many working days there are between any two dates so you can include a working day number in your calendar. Let’s find out how to calculate working days in Power Query.

Table of contents

• Function for Working Days logic
• Working Days Code Examples
  • Workday Number of Week/Month/Year
  • Add Holiday Dates
• Conclusion

Function for Working Days logic

To create a NetWorkDays function in Power Query, you will put List functions to work. To improve your Power Query foundations, I always advise people to master List Functions. They are useful in many scenarios, and a re often helpful in your solutions.

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 working days from the starting date up to and including the 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 that follows below has some slight adjustments.

The first line specifies the parameters for the function. This allows us to call the workday logic as a custom function easily.The next difference is, that 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, the below function uses an empty list. Also, here the COALESCE operator helps in preventing errors.

The final working day 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

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.

Add Holiday Dates

In some net working-day 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 situations, you will want to reference a table with holidays, or a list with holidays.

Holidays Table

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

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

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.

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

Conclusion

That’s it! Now you know how to calculate the number of working days between two dates in Power Query. You created a DATEDIFF function in M. It’s easy to use in your regular date table and helps you get the exact number of days you need. By combining list functions, including error checking and turning logic into a function, you can now easily find the number of working days between two days.

If you enjoyed this, you may enjoy learning how to return the Nth business day starting from a given date.

Happy querying!