Two ways to total working days in a year using Excel formulas

An annual count is one formula. That is the problem: one formula returning one number gives you nothing to check it against, and the number is plausible whatever it is.

So this page gives two ways to compute it that fail differently, plus the arithmetic that says which of the three possible answers you should have got.

What to take away

The one-line version

For a year in A2:

=NETWORKDAYS(DATE(A2,1,1),DATE(A2,12,31))

Both ends are counted, so no adjustment is needed at either boundary. Adding a day to "reach" 31 December pushes the span into the next year and adds whatever weekdays it finds there.

To subtract closures, put them in one range and pass it as the third argument:

=NETWORKDAYS(DATE(A2,1,1),DATE(A2,12,31),Closures)

Dates in that range outside the year are ignored, so one multi-year range can serve every year in the sheet without being trimmed. Dates in it that fall at a weekend are also ignored, because rest days are removed first and a date already removed cannot be removed twice.

The version that shows its work

The same count without NETWORKDAYS:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(A2,1,1)&":"&DATE(A2,12,31))),2)<6))

It builds every date in the year, asks each one for its weekday number, and counts those below six. This works because dates are stored as consecutive integers, the same day-numbering idea astronomers have used for centuries: a date range is a range of integers, so a spreadsheet can walk it with row numbers.

WEEKDAY with a second argument of 2 numbers Monday as 1 through Sunday as 7, which makes the comparison readable. With the default argument, Sunday is 1 and the test has to change. That silent renumbering is a common source of an answer out by 52.

This version is slower and longer. Its value is that the weekend rule is visible in the formula rather than baked into a function name, so a reviewer can see what was assumed. When the two versions disagree, one of them has a weekend convention you did not intend.

What the answer should be

Work it out before you look. Fifty two whole weeks give 260 weekdays. A common year has one day left over and a leap year two, so the answer is 260 plus the weekdays among those leftover days, which are the year's first day and, in a leap year, its second.

A leap year with fewer weekdays than the common year before it is not an error. The derivation, and the full table of fourteen cases, is under the annual formula.

Then check the total against the sum of twelve monthly counts computed separately. The two must agree. A mismatch is almost always a month boundary rather than the year, and the monthly side is under counting a month.

Non-standard weekends

NETWORKDAYS.INTL takes a weekend argument, either a code or a seven-character string read Monday to Sunday where 1 marks a rest day:

=NETWORKDAYS.INTL(DATE(A2,1,1),DATE(A2,12,31),"0000110",Closures)

Only 0 and 1 are allowed in the string, and it is always seven characters long. All ones is accepted and returns zero, not an error. A typo that stays valid gives a confident wrong answer.

Check the string on a single known week before you point it at a year. Which convention applies where is not a spreadsheet question; it is under national calendar differences.

Three things that go wrong at annual scale

A closure list that covers only part of the year silently under-subtracts. The formula cannot tell a year with few closures from a list that stops in June, so record the effective period of the list beside it.

A year built from a text date in A2 produces a value error rather than a wrong number, which is the good case. A year built from a text date inside the closure range produces the same error and is harder to find, because the range is usually somewhere else in the workbook.

An annual figure copied into next year's model without re-running is the expensive one. The formula is right, but the answer is stale. Nothing in the cell says so.

The versioning discipline for the closure list is under state and regional calendars. The wider serial-number contract these formulas rely on is under spreadsheet and interface tools.

The stale-answer problem is exactly what regression testing is for: keep a small sheet of years whose answers you have derived by hand, and re-run it whenever the closure range is touched.

Common questions

Why does my leap year return fewer working days than the year before?

Because 29 February is not the day that decides it. The year's first one or two days are, and a leap year starting at a weekend loses both of them.

Can I count a fiscal year rather than a calendar year?

Yes, and the function does not care. Give it the two dates you mean. The three-answer rule above applies only to calendar years, so derive the expected figure for a fiscal year from its own start day and length.

Should the closure range be a named range?

It should be one range referenced from everywhere, whatever you call it. The failure this prevents is twelve monthly formulas that were edited at different times.

What does the count mean if the closure range is empty?

It is a weekday count, not a working-day count. That is a legitimate number to want, but it should be labeled as what it is.