Function to calculate the number of working days between two dates

Henry Simms
Henry Simms Administrator
edited December 2023 in General

Recently I wanted to create a function in Data Studio to calculate the number of working days between two dates, similar to Excel's NETWORKDAYS function. First, a couple of comments:

  1. Weekend days are defined as Saturday and Sunday. Being able to handle alternative weekend days (like the Excel  NETWORKDAYS.INTL function) will be a future project
  2. Holidays are not taken in to account, again something for implement in future!
  3. Like NETWORKDAYS, my function will include both the start date and end date when calculating work days, so Monday to the following Friday is 5 days, and Friday to the next Monday is 2 days
  4. Like NETWORKDAYS, if the start date is before the end date, we return a negative working day count
  5. Unlike NETWORKDAYS, if start and end days are the same and are a weekday day we return 1 (rather than -1). This seems like a better result (highlighted in the screenshot below)

Here's the results from my function, showing the result from Excel's NETWORKDAYS function in column 4 and the results from our Data Studio equivalent function in column 5:

image.png

Row 13 shows how both functions handle invalid dates in the input.

Function's can be imported from this dmx:

Calculate Net Working Days v3.dmx

The test data file used for the function can can be downloaded from here, if you plan to make any changes.

Test Working Days Calc.xlsx

The Data Studio function is called "Excel NETWORKDAYS" and uses 3 saved sub-functions

  • Get date of previous working day
  • Get date of next working day
  • Get date of Monday in week

The function and sub-functions are documented (summary and description) and include test data for parameters. When imported into your system they should look like this:

image.png

The main function itself is a little complex, so I expect it can be simplified. The approach I took was roughly:

  1. Calculate full weeks (Monday to Monday) between the end date and start date, and number of weekend days (2 out of 7)
  2. Calculate the adjustment based on the actual dates, based on the working day closest to the given date (previous working day for start date, next working day for end date)
  3. Take the adjusted days from the full week days calculation, and add 1 because Data Studio's Date Compare function does not include the end date
Tagged:

Comments

  • Henry Simms
    Henry Simms Administrator

    Note: I've updated the functions to parse start and end dates, so it can now handle scenarios where these values are not already correctly typed

Categories