Working days in year

=NETWORKDAYS(DATE(year,1,1),DATE(year,12,31),holidays)
To calculate the number of working days in a year, you can use the NETWORKDAYS function. NETWORKDAYS automatically excludes weekends and holidays if they are provided. In the example shown, the formula in E5 is:
=NETWORKDAYS(DATE(D5,1,1),DATE(D5,12,31),holidays)
Where D5 contains a year, and holidays is the named range E5:E14.
Note: NETWORKDAYS includes both the start and end dates in the calculation if they are workdays.
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:
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 (Staturday 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 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.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.