Suppose we want to count only the number of “working” days? We want to exclude the weekends and the holidays if any.
When it comes to letting Excel know your specific holidays (technically, these can be ANY days you wish to skip, not just sanctioned holidays), you need to create a list of the unwanted days somewhere in your workbook.
Below we have created in cells D7 through D9 a shortlist of days we will ignore during the calculations.
Enter the NETWORKDAYS Function
The NETWORKDAYS function returns the number of whole working days between a start_date and end_date. Working days exclude weekends and any dates identified in holidays.
This function has been around for quite some time, which means it has gone through some changes.
Considering not all users observe the same number of days for a weekend, much less the same days of the week, a more robust version of the NETWORKDAYS function was developed. Welcome the NETWORKDAYS.INTL function to the party.
The NETWORKDAYS.INTL function, introduced around Excel 2010, has an extra argument that allows you to define the days those weekends are observed. The original NETWORKDAYS function is hard-coded to observe weekends on Saturday and Sunday.
The syntax for the NETWORKDAYS.INTL function is as follows:
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
The NETWORKDAYS.INTL function syntax has the following arguments:
start_date and end_date – Required. These are the dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
[weekend] – Optional. Indicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. [weekend] is a weekend number or string that specifies when weekends occur.
[holidays] – Optional. An optional set of one or more dates that are to be excluded from the working day calendar. Holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial values in holidays can be arbitrary.
For the [weekend] argument, below is a table of codes that define the various weekend combinations.
Calculating a Date Difference Without Default Weekends
Let’s start with a simple calculation to determine the number of days between June 2nd and June 14th.
If we wish to accept the default “Saturday/Sunday” weekend combination, we don’t have to add anything extra to the formula as those days are the function defaults.
NOTE: When using the functions to determine the number of days, the first day and last day (i.e., the [start_date] and [end_date]) are counted in the result.
Now let’s calculate the number of days between June 2nd and June 14th excluding the weekends (5th, 6th, 12th, & 13th), as well as the holiday dates listed in cell range D7 through D9.
As we enter the same formula from above when we reach the third argument [weekends] we are presented with a list of the two-day and single weekend combinations.
We will stick with the default “Saturday/Sunday” combination” and select the number 1 code.
For the fourth argument [holidays], we select the cell range D7:D9 and lock it as an absolute reference so it doesn’t change when we repeat the formula for other examples.
=NETWORKDAYS.INTL(A2, B2, 1, $D$7:$D$9)
Filling the formula down to the next example, we see that 3 days qualify as working days.