Excel tips: how to use NETWORKDAYS function

Question

Is it possible to calculate the number of weekdays in a month?

Answer

Excel’s NETWORKDAYS function is built to handle these types of calculations. It has an option to take into account public holidays, so it can also calculate workdays.

Excel 2010 introduced a new function called NETWORKDAYS.INTL that handles different weekends or different days to exclude. Note: some Middle Eastern countries have Friday and Saturday as their weekend

To use the NETWORKDAYS function in Excel 2003 you must install the Analysis Toolpak Add-in. See Excel’s Help system for instructions.

Syntax

=NETWORKDAYS(Start_Date,End_Date,Holiday_Range)

=NETWORKDAYS.INTL(Start_Date,End_Date,Weekend,Holiday_Range)

Start_Date and End_Date are usually cell references containing dates.

Weekend (NETWORKDAYS.INTL only) is optional and is a number that determines the day or days to be ignored.

Figure 1 shows a list of Weekend number options. If omitted, the default is 1.

The Holiday_Range is optional and is a range that contains a list of Public Holidays dates. If you don’t include the Holiday Range then weekdays will be calculated.

The function will return the #VALUE! error if either the Start_Date or End_Date are not dates. It will return a negative if the Start_Date is after the End_Date.

Figure 2 has a table of the current financial year’s dates with calculated weekdays and workdays.

The holidays during the financial year are listed in Column I and are for Western Australia.

This table shows formulas in Figure 2 cells.

These formulas have been copied into all the cells down to row 13 and to row 16. Row 14 adds up rows 2 to 13.

Columns B and D are there for information purposes only and are not part of the calculation. Row 16 calculates the days for the financial year and is a check for the totals in row 14.

How many Sundays in a month?

You can use the NETWORKDAYS.INTL function to calculate how many of a particular day there are in a month. For row 2 in Figure 2 you could use

=C2-A2+1-NETWOR KDAYS .INTL (A2,C2,11)

This formula subtracts the start date from the end date and then adds 1. It then subtracts the result of the NETWORKDAYS.INTL function, which treats Sundays as weekends and doesn’t have any holidays.

The reason you add 1 is that when you subtract one date from another date, the result is exclusive. When you subtract 1/7/2013 from 31/7/2013 the result is 30, not 31. Hence you need to add back 1 to equal the total days in the month or period involved.

This technique works for any two dates. You can change the Weekend number in the function to count different days between the two dates.

Neale Blackwood CPA is CPA Australia’s resident Excel expert. He runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].