Exceljet

Quick, clean, and to the point

Workdays per month

Excel formula: Workdays per month
Generic formula 
=NETWORKDAYS(date,EOMONTH(date,0),holidays)
Explanation 

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

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

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

How this formula works

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(B4,0)

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables