Where “holidays” is the named range E3:E13. 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
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.
