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


Where "holidays" is the named range E3:E13.

Generic formula



First, it's important to understand that the values in the Month column (B) are actual dates, formatted with the custom number format "mmm".

For example, B4 contains January 1, 2014, but displays only "Jan" per the custom number format.

The formula itself 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).

For each month, the start date comes from column B and the end date is calculated with the EOMONTH function like so:


EOMONTH takes a date and returns the last day of a month. The month itself is controlled by the 2nd argument. Since in this case we want to stay in the same month, we use zero.

Finally, a list of holidays is provided as the 3rd argument to NETWORKDAYS using the named range holidays (E3:E13).

With this information, NETWORKDAYS calculates the number of working days in each month, automatically excluding weekends and holidays.

If you need more control over which days are treated as weekends, use the NETWORKDAYS.INTL function.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.