Country calendars in Google Sheets: taming the weekend argument first

A sheet built in one country and reused in another is usually wrong in two places at once: the weekend is still Saturday and Sunday, and the closure range still holds the first country's dates.

Neither failure raises an error. Both produce a plausible number. This page is about making them visible.

What to take away

The weekend argument

<figure class="inline-figure">
  <img src="/inline/country-calendars-google-sheets-fig1-1280.webp" alt="Table mapping seven-character weekend strings to their rest days (Country calendars in Google Sheets: taming the weekend argument first)" loading="lazy" decoding="async" />
  <figcaption>The third argument is read Monday to Sunday, with 1 marking a rest day. <a href="https://workdaydatecalculator.com/image-licence">Image: Workday Date Calculator</a></figcaption>
</figure>
=NETWORKDAYS.INTL(Start, End, "0000011", Closures)

The third argument is seven characters read from Monday to Sunday, where 1 marks a rest day and 0 a working day. Only those two characters are accepted, and the string is always seven long.

All ones is accepted and returns zero rather than raising an error, so a typo that happens to stay valid gives a confident wrong answer. Test any new string against a single week you can count: a Monday to Sunday span should return seven minus the number of ones in the string.

The convention itself is not arbitrary and not universal. Which days a country treats as rest days is a matter of law and custom rather than of arithmetic, and the range of practice is set out under workweek and weekend.

Put the string in a cell rather than in the formula. A convention typed into forty formulas is a convention that cannot be changed, and it is the single most common reason a sheet cannot be reused for a second country.

What the weekend argument changes in 2027

Against 2027, with closures ignored so that only the weekend rule is in play:

Two-rest-day conventions agree over a whole month and disagree over a short span, which is the opposite of what most people expect. That is why a sheet tested only on month totals will pass with the wrong string in it. The reasoning behind those figures is under the cross-country formula and, for the annual case, counting a year.

One closure range per calendar

Give each calendar its own named range and a row in a small table beside it:

Then let a row pick its calendar by name and resolve the two arguments with a lookup. The alternative, one merged range containing every country's dates, subtracts every country's closures from every count, and the resulting numbers are low in a way nobody can attribute.

=NETWORKDAYS.INTL(Start, End, VLOOKUP(C2, Calendars, 2, FALSE), INDIRECT(VLOOKUP(C2, Calendars, 3, FALSE)))

C2 holds the calendar name for that row and Calendars is the table above. The closing FALSE in each VLOOKUP demands an exact name, so a mistyped country returns an error rather than a silent default. INDIRECT reads the range name in the third column and passes that range to the count.

Dates in a closure range that fall outside the span are ignored. Dates on a rest day are ignored too, because rest days are removed first.

Where the lists come from and how they are versioned is under how closure calendars are made.

Public lists cover these closures. In the United States, the Office of Personnel Management publishes the federal holiday list. In Canada, Employment and Social Development Canada publishes the holidays that federally regulated workplaces observe. Exchanges keep their own calendars, and the New York Stock Exchange calendar includes Good Friday.

The parsing trap

A date typed or pasted as text is converted using the file's locale. The string 03/05/27 is eight characters. A day-first file reads it as 5 March 2027. A month-first file reads the same eight characters as 3 May 2027. Both files show a valid date afterward.

The underlying convention that makes an unambiguous order possible is ISO 8601, which orders the components from largest to smallest and therefore sorts correctly as text as well as parsing unambiguously. The same day written as 2027-03-05 reads the same way in every file.

Checking a country sheet

Three tests catch nearly everything.

A week that you counted by hand should return seven minus the number of rest days in the string. A span made entirely of rest days should return zero.

And the twelve months of a year, summed, should equal the annual count computed in one call, which catches a boundary error at a month edge. The expected monthly figures are under counting a month, and where the same numbers have to survive a trip into an interface, the type contract is under spreadsheet and interface tools.

Common questions

Can I use the numeric weekend codes instead of the string?

You can, and they are shorter. The string is worth the extra characters because it can be read by someone who does not have the code table in front of them.

What if a country's rest days are not the same every week?

Then the weekend argument cannot express it, and those weeks belong in the closure range as exceptions. This is common where a rest day moves for part of the year.

Should I keep a country column even when the sheet covers one country?

Yes. The sheet that covers one country today is the sheet someone reuses for two next year, and a missing column is harder to add later than an unused one is to carry.

Does the file timezone affect these counts?

Not for typed dates, which carry no zone at all. It affects anything derived from the current moment, and that is a separate class of problem.