Working days in year

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.

How this formula works

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 (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.