Excel Tips & Tricks #485 – Refreshing NETWORKDAYS and WORKDAY functions

In this article we cover the definitions of the NETWORKDAYS and WORKDAY functions, explain the difference between them, and provide specific examples of their use. This topic was last covered in Tip #89 and Tip #47.

Refresher: dates as numbers

Before we dive into NETWORKDAYS and WORKDAY it is worth reminding ourselves that dates in Excel are stored as numbers.

As with normal numbers we can perform arithmetic with dates. For example we can subtract one date from another to get the number of days between them.

Notice that the formula in cell B7 (as shown on the right hand side) is simply the date in B5 minus the date in B3. This returns the number of days between the two dates.

Similarly we can add numbers to dates to count a specified number of days forward or backwards.

In this example we have calculated in cell B7 the date that is 100 days in the future from today's date, in cell B3.

Where NETWORKDAYS and WORKDAY are useful, is that they provide conceptually similar calculations but automatically exclude non working days (eg, weekends).

NETWORKDAYS

The NETWORKDAYS function calculates the number of working days between two dates.

The syntax of the NETWORKDAYS function is:

=NETWORKDAYS( start_date , end_date , [holidays] )

Consider a scenario where we have a project to complete between a given start date and end date and want to know how many working days are available to complete the project.

In this example let's assume that our project needs to be worked on in May 2024, so our start date will be 01/05/2024 and our end date will be 31/05/2024.

We can enter the start dates and end dates in cells C2 and C3 and refer to them in our NETWORKDAYS function in cell C5.

Here you can see the result of 23 working days.

Let's convince ourselves that this is true by actually counting the days on a calendar.

Looking at the month of May 2024 we can see that there are 3 working days in the first week, and then four full weeks of 5 working days. As expected, this all adds up to 23!

This also demonstrates the fact that the start dates and end dates are included in the calculation (if they fall on working days, otherwise they make no difference).

Adjusting for non-working days

The third argument in the NETWORKDAYS function allows us to specify a range of cells containing dates that are to be excluded from the calculation.

This may be useful if, for example, we want to exclude bank holidays, or days that an employee has booked as leave.

We can refine our project example by including a list of UK bank holidays for 2024, as shown below with the blue outline.

This list has been referred to in the third argument of the NETWORKDAYS function with the range reference C9:C16.

Where any of these dates fall between our start date and end date they will be excluded from the count of working days.

In this case the two dates highlighted yellow fall in May 2024 so have been excluded, reducing the result from 23 to 21 working days.

Again, this can be confirmed visually by highlighting the relevant days on the calendar month. The bank holiday days (shown with a blue outline) have been excluded.

NETWORKDAYS vs WORKDAY

A related function for calculation with workdays is the WORKDAY function.

The syntax of the WORKDAY function is:

=WORKDAY( start_date , days , [holidays] )

The WORKDAY function will take a given start date and return the value of another date that is separated from the start date by a given number of working days.

The WORKDAY function is useful in the same scenarios as for NETWORKDAYS. In both cases you have two dates separated by a number of working days. The choice of function will depend on what you are trying to calculate:

• If you know the start date and end date and need to calculate the number of working days between them – use NETWORKDAYS
• If you know either the start date or end date and need to calculate the other one based on a number of working days – use WORKDAY

Let's demonstrate the WORKDAY function in a scenario where you have a monthly reporting deadline that is always 5 working days after the month end. You would like to know where the monthly deadlines will fall for each month in 2024.

This can be achieved as follows:

The reporting deadlines, highlighted yellow, have been calculated using the WORKDAY function. This has been entered in cell C4 then the formula has been copied down to all the cells highlighted yellow.

The arguments in the WORKDAY function have been entered as follows:

• start_date

  – the adjacent cells in column B under the "Month end" heading.

• days

  – entered as the number 5.

• [holidays]

  – entered in cells H4:H11. As with NETWORKDAYS this is an optional argument. Note the $ signs in the formula, allowing us to drag the formula down without changing the cell reference containing the list of holidays.

NETWORKDAYS and WORKDAY functions – international versions

As we have seen above both the NETWORKDAYS and WORKDAY functions can simplify the process of counting working days.

Both of these functions have international counterparts NETWORKDAYS.INTL and WORKDAY.INTL that provide further flexibility around choosing which dates are counted as weekends or should be treated as nonworking days.

Further information on these international variants has been covered in Tip #89.