Multiple working calendars in one Excel sheet: WORKDAY.INTL weekend strings

Excel can handle several working calendars in one sheet without any add-in. It cannot supply the closure dates, and it never will: those are yours to bring. What it gives you is a clean way to hold a different working week and a different list of closed days for each calendar, and to switch between them with one cell.

Formulas below are copyable. The holiday dates are not here, for the reason set out at the end.

What to take away

The weekend string

WORKDAY and NETWORKDAYS hard-code a Saturday-Sunday weekend. Their .INTL variants take a working week as an argument, which is the whole trick:

=WORKDAY.INTL(start, days, weekend, [holidays])
=NETWORKDAYS.INTL(start, end, weekend, [holidays])

The weekend argument accepts a seven-character string of 0 and 1, read Monday through Sunday, where 1 marks a non-working day:

Any pattern works, including non-adjacent days off and a seven-day week. Because it is just a string, you can store it in a lookup table and never touch a formula again when a calendar changes.

The two-table layout

Two small tables carry everything.

Calendars, one row per calendar, with columns Id and Weekend:

GBLDN   0000011
XX-A    0000110
XX-B    0000001

[Closures](/country-calendars-holidays-2027), one row per closed day, with columns Id, Date, Note. The Note column is not decoration: it is where you record which published list the date came from and when you took it.

Then the working formula, on a row that has a Start, a Days and a Calendar:

=WORKDAY.INTL(
    [@Start],
    [@Days],
    XLOOKUP([@Calendar], Calendars[Id], Calendars[Weekend]),
    FILTER(Closures[Date], Closures[Id]=[@Calendar])
 )

FILTER returns the subset of dates for that calendar as a spilled array, which WORKDAY.INTL accepts directly. That is what lets one formula serve every calendar in the book.

On a version without FILTER and XLOOKUP, use INDEX/MATCH for the weekend string and keep one column of dates per calendar instead:

=WORKDAY.INTL([@Start], [@Days],
    INDEX(Calendars[Weekend], MATCH([@Calendar], Calendars[Id], 0)),
    Closures_GBLDN)

Less elegant, entirely reliable, and easier for a colleague to audit.

Counting between two dates

The mirror function takes the same arguments:

=NETWORKDAYS.INTL([@From], [@To],
    XLOOKUP([@Calendar], Calendars[Id], Calendars[Weekend]),
    FILTER(Closures[Date], Closures[Id]=[@Calendar]))

Expect an off-by-one against WORKDAY.INTL, and expect it to be correct. NETWORKDAYS.INTL counts both endpoints. WORKDAY.INTL counts neither the start day nor beyond.

Ask for five working days after a Monday, then count back over it and you will be told six. Neither function is wrong: they answer different questions. A sheet that treats one as a check on the other reports a phantom error every time.

One month from the 31st

Working-day formulas are not the only place a calendar assumption hides. "One month later" has two defensible answers, and Excel will give you either:

=EDATE(A2, 1)                              clamps to the end of the month
=DATE(YEAR(A2), MONTH(A2)+1, DAY(A2))      rolls over into the next month

From the 31st of January, EDATE returns the last day of February. The DATE version asks for a February day that does not exist and rolls forward into March by however many days February is short.

Both behaviors are used in real agreements. Contracts that say "one month" without saying which one are relying on everybody guessing the same way. EOMONTH(A2, 1) gives you a third reading, the end of the following month, which is what many payment terms actually mean.

Try all three in adjacent cells with a month-end date. It takes ten seconds and is the fastest way to see why this clause causes arguments.

What breaks these formulas

Dates stored as text. The commonest failure. Text in the holidays range is not an error: it is silently not a date, so those closures are ignored and the answer comes back plausible and wrong. Check that every date column right-aligns by default.

A holidays range that does not span the window. If you count backwards past the earliest date in your list, or forward past the latest, the missing region is treated as ordinary working days. No warning.

Stale hard-coded dates. A closure list typed directly into a formula survives every copy of the workbook and is never revisited. Keep it in a table with an as-of date in a visible cell.

Duplicates and weekend entries in the holidays range are harmlessly ignored, so you can paste a whole published list without pruning it.

Why there are no dates on this page

The formulas are general. The calendars are not. Closure lists differ by country, by region within a country, and between a bank, a court and an employer in the same town, and they get amended at short notice.

Take yours from the body whose deadline you are counting toward, or the government list it follows. Paste it into the Closures table with the source and the date you took it.

Paste it in ISO 8601 order if you have the choice. That ordering parses the same way under every locale and sorts correctly even while it is still text.

Which list applies and how to pin it down covers the choice. A sheet with the wrong list is more dangerous than a sheet with no list, because it produces an answer.

A test block worth keeping

Put four hand-worked cases at the bottom of the sheet: a Friday start, a weekend start, a count of zero, and one that crosses a closure. Give each the expected answer beside it and a comparison flag. When someone edits the weekend strings or repoints the tables, the flags go red before anyone ships a wrong date.

Worked comparisons across working weeks gives you cases to seed it with.

Common questions

Why does the weekend string start at Monday?

Because the function defines it that way, and because a week that begins on Monday is the convention the international standard uses for numbering days and weeks, as set out under ISO week date. It is a source of confusion where a local calendar shows Sunday first.

What happens if the string is not exactly seven characters?

The function raises an error, which is the good outcome. The dangerous case is a string that is seven characters and wrong, because it returns a confident number.

Can one workbook hold several countries?

Yes, and it should: one row per calendar with its own weekend string and its own closure range, and a lookup that picks them per calculation. What fails is a single merged closure range, because it subtracts every calendar's closures from every count.

Do I need to prune weekend dates out of a pasted closure list?

No. Dates falling on rest days are ignored, as are dates outside the span, which is what makes a published list safe to paste whole. The reasoning about which list to paste is under how country calendars work.

How do I check the sheet is applying the string at all?

Count a single week. The answer must be seven minus the number of ones in the string, and if it is five whatever the string says, the plain function is being used somewhere. The day-level arithmetic behind the expected answers is under counting a month.