NETWORKDAYS
The NETWORKDAYS function is used to calculate the number of business days (working days) between two dates, optionally subtracting a list of holiday dates.
The function takes three arguments: the start date, the end date, and an optional list of holiday dates.
The first argument, startDate, is the start date of the period to be considered. It must be a valid DateTime value.
The second argument, endDate, is the end date of the period to be considered. It must also be a valid DateTime value.
The third argument, holidays, is an optional list of holiday dates to be subtracted from the total. It can be provided as a variable number of arguments using the params keyword. Each holiday date must also be a valid DateTime value.
The function returns an integer value representing the total number of business days between the start and end dates, excluding the holidays provided.
Syntax
int? NETWORKDAYS(DateTime? startDate, DateTime? endDate, params DateTime[] holidays)
Inputs
| Object | Data Type | Description |
|---|---|---|
| startDate | DateTime | A start date value. |
| endDate | DateTime | An end date value. |
| holidays | DateTime | A list of holidays to subtract. |
Example
Say you want to calculate how many business days there are between the start of the year and end of the year. The dates might be different for each sector of a business so needs to be calculated row by row.
If we take the example of a data set containing the following columns: ID, SectorName, StartofYear, EndofYear, Holidays. With Holidays being an array of dates that are the holidays within that year.
You can calculate the number of business days using the following:
NETWORKDAYS(StartofYear, EndofYear, Holidays)
This will calculate the number of business days between the date in StartofYear and date in EndofYear.
If you then wanted to calculate the number of business days with the holidays discounted you can do so with the following:
NETWORKDAYS(StartofYear, EndofYear, Holidays)
This will calculate the number of business days between the date in StartofYear and EndofYear, excluding the days included in the Holidays column.