Excel formula: Working days in year

Generic formula

=NETWORKDAYS(DATE(year,1,1),DATE(year,12,31),holidays)

Summary

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.

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

Author

Dave Bruns

Related formulas

Workdays per month

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

Get workdays between dates

To calculate the number of workdays between two dates, you can use the NETWORKDAYS function. NETWORKDAYS automatically excludes weekends, and it can optionally exclude a custom list of holidays as well. For example, if you have the date January 4,...

Get work hours between dates

This formula uses the NETWORKDAYS function calculate total working days between two dates, taking into account weekends and (optionally) holidays. Holidays, if provided, must be a range of valid Excel dates. Once total work days are known, they are...

Add business days to date

By default, the WORKDAY function will exclude weekends (Saturday and Sunday). In this case, we have also supplied a list of 3 holidays that all fall in the date range being calculated, which means both the weekends and holidays will be excluded. The...

Get work hours between dates custom schedule

At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings, Sunday=1 and Saturday = 7. The...

List holidays between two dates

At a high level, this formula uses a nested IF function to return an array of holidays between two dates. This array is then processed by the TEXTJOIN function, which converts the array to text using a comma as the delimiter. Working from the inside...

Related functions

Excel NETWORKDAYS Function

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.

NETWORKDAYS ...

Excel NETWORKDAYS.INTL Function

The Excel NETWORKDAYS.INTL function calculates the number of working days between two dates. NETWORKDAYS.INTL excludes Saturday and Sunday by default, but provides a way to specify which days of the week are considered weekends. The function can...

Excel DATE Function

The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.

Excel TODAY Function

The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. You can format the value returned by TODAY using any standard date format. If you need current...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.