Notes on working days per month in Excel: NETWORKDAYS, EOMONTH and month edges

A month is the worst span to hand a spreadsheet, because the two dates that define it are not typed in. They are derived, and the function that derives them has edge behavior of its own.

This page is about that. The counting is trivial. The failures are all at the boundary, in the type of what is in the cell, and in the argument nobody reads twice.

What to take away

The month, expressed as two cells

Given a year in A2 and a month number in B2:

first day  =DATE(A2,B2,1)
last day   =EOMONTH(DATE(A2,B2,1),0)
weekdays   =NETWORKDAYS(DATE(A2,B2,1),EOMONTH(DATE(A2,B2,1),0))

EOMONTH with a second argument of zero gives the end of the month the date is in. One gives the end of the next month, minus one the end of the previous.

That argument is why you use EOMONTH instead of DATE(A2,B2+1,1)-1: December needs no hand-carried year, February no leap-year check.

DATE itself normalizes out-of-range arguments, so DATE(2027,13,1) is January 2028 and DATE(2027,3,0) is the last day of February. That is convenient and it is also how a month-number off-by-one hides: a loop that runs B2 from 0 to 11 produces twelve correct-looking months, shifted by one.

Both ends count

NETWORKDAYS(start, end) includes the start and the end. The convention matters most at a month edge, where the two dates are generated rather than typed, and where a stray plus one is invisible.

That last row is worth knowing: reversing the arguments raises no error and returns no zero. It returns a negative count, so a monthly total summing twelve such calls is wrong by twice the size of any reversed span.

The single-day rows are the ordinary fencepost problem in calendar form. A sheet that gets them right at a month edge usually gets everything else right too.

The weekend argument

NETWORKDAYS has Saturday and Sunday baked in. [NETWORKDAYS.INTL](/country-calendars-google-sheets) takes a weekend argument, either a numeric code or a seven-character string read from Monday to Sunday, where 1 marks a rest day and 0 a working day.

String	Rest days

Only 0 and 1 are accepted, and the string is always seven characters. The all-ones case is defined, not an error. A mistyped string that happens to be valid returns a confident zero.

Check the weekend argument by counting a week you already know. A Monday to Sunday span returns seven minus the number of ones.

The holidays argument

The optional last argument takes a range of dates or an array of them. Three properties are worth knowing before you rely on it.

A closure on a rest day is not subtracted twice. The function removes rest days first, then removes listed dates still standing, so a Saturday in the list changes nothing.

A listed date outside the span changes nothing either. That lets one sheet-wide closure range be pointed at from every month without trimming it per month.

Text in that range is the one that bites. A cell holding 2027-03-15 as text is not a date to the function even though it reads as one to you, and the function raises a value error rather than skipping it. Wrap the range in a check that every entry is numeric before you trust a count that changed.

What goes in that range is not a spreadsheet question. It comes from whichever body set the deadline, and the reasoning is under how closure calendars are made and, for the sub-national layer, state and regional calendars.

Checking the sheet against something else

Build the twelve months down a column and sum them. A common year totals 261 or 260; a leap year, 262, 261, or 260.

If your sum is 365, the weekend argument is not applied. If it is 260 for a year you expected to be 261, check the year's first and last day instead of the formula: a year starting and ending on a weekend day loses one.

The reasoning behind those totals is under counting a year, and the closed form behind the monthly figures is under counting a month.

The serial-number contract that all of this rests on, and what changes when the same numbers cross into an interface, is under spreadsheet and interface tools.

Common questions

Why does my month count look like a large integer?

Because EOMONTH returns a date serial and the cell is formatted as a number. Format the cell as a date. Nothing is wrong with the value.

Are Excel serial numbers the same as a true day count?

Not at the very bottom of the range. The 1900 date system keeps a day that never existed, a documented leap year defect inherited for compatibility, so serials before the start of March 1900 are one out against a real day count. Above that date the two agree.

Can I count a month without generating its two ends?

Not with these functions. They take a span. Deriving the ends with DATE and EOMONTH is the shortest reliable way, and it is also the part that should carry a test.

Does the function know about my company's closures?

No. It knows about the dates you hand it in the last argument, and about nothing else. A blank last argument means a weekday count, not a working-day count.

What happens if the same closure date appears twice in the range?

It is removed once. Duplicates in the list are harmless, which is useful when two published lists are stacked into one range rather than merged by hand.