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.
Explanation
NETWORKDAYS is a built in function accepts a start date, end date, and (optionally) a range that contains holiday dates.
In this case, the start date is Jan 10, 2018, provided as cell B5. The end date is calculated using the EOMONTH function with an offset of zero, which returns the last day of the month of the date given. A list of holidays is provided as the range E5:E14.
The EOMONTH function resolves to Jan 31, 2018, and the number of working days between Jan 10 and Jan 31 (inclusive) is 16. One of those working days (Jan 15) is listed as a holiday, so NETWORKDAYS returns a final result of 15.
Working days remaining this month
To return the working days that remain in the current month, the formula can be adapted with the TODAY function as follows:
=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),holidays)
Custom workdays / weekends
To work with custom weekends (i.e. weekends are Sunday and Monday, etc.) switch to the more robust NETWORKDAYS.INTL function, which allows control over which days of the week are considered workdays. See NETWORKDAYS.INTL for more information.
Need a date?
If you need to calculate a date n workdays in the past or future, see the WORKDAY function.
