Explanation
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.
Explanation
NETWORKDAYS is a built-in function accepts a start date, an end date, and (optionally) a range that contains holiday dates. In the example shown, we generate the start and end date using the DATE function like this:
DATE(D5,1,1) // first day of year
DATE(D5,12,31) // last day of year
The DATE function returns these dates directly to the NETWORKDAYS function as start_date and end_date, respectively.
Holidays are supplied as a list of dates in E5:E14, the named range holidays .
NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and dates supplied as holidays and returns a total count of working days in the year 2019.
No holidays provided
The formula in E6 returns a higher working day count because holidays are not supplied:
=NETWORKDAYS(DATE(D6,1,1),DATE(D6,12,31))
Working days remaining this year
To return the working days that remain in a given year, the TODAY function can be used to generate a start date like this:
=NETWORKDAYS(TODAY(),DATE(D5,12,31),holidays)
Custom workdays/weekends
To work with custom weekends (i.e. weekends are Sunday and Monday, etc.) switch to the more powerful NETWORKDAYS.INTL function , which allows control over which days of the week are considered workdays.
