How to Calculate Workdays per Month

We need to calculate the workdays per each month in list.

Solution

To calculate workdays per month, we will use the EOMONTH function together with the NETWORKDAYS function. In the example shown, the formula in B5 is:

=NETWORKDAYS(A5,EOMONTH(A5,0),holidays)

Explanation

The values in the Month column (A) are actual dates, formatted with the custom number format "mmm". For example, A13 contains September 1, 2016, but displays only "Sep".

The formula is based on the NETWORKDAYS function, which returns the number of working days between a start date and end date, taking into account holidays (if provided).

The syntax of the function is:

NETWORKDAYS( start_date, end_date, [holidays] ) where:

start_date - The start date, from which to count the number of workdays.

end_date - The end date, to count the number of workdays up to.

[holidays] - An optional argument, which specifies an array of dates (in addition to weekends) that are not to be counted as working days.

For each month, the start date comes from column B and the end date is calculated with the EOMONTH function like so: EOMONTH(A5,0) which takes a date and returns the last day of a month (for the 2nd argument we use zero to stay in the same month).

A list of holidays is the 3rd argument to NETWORKDAYS (the named range holidays (D5:D10)).

If we need take into account custom weekends (i.e. weekends are Friday and Saturday, Sunday or Monday only etc.) we'll need to switch to the NETWORKDAYS.INTL function.

Common Errors of NETWORKDAYS Function is

#VALUE!    -       Occurs if any of the supplied arguments are not valid dates.