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