Building state calendars in Excel with one column per layer

What to take away

One column of dates per layer, plus a small registry sheet. The merge happens inside the formula, at the count.
Stack whole ranges into the holidays argument with union brackets: (Cl_TX,Cl_Fed).
Each row names its layers in slot columns. MATCH and CHOOSE resolve them, so nobody edits a formula when a layer changes.
Stacking is idempotent. A date named twice is removed once, in either slot order.
No formula can detect a layer applied outside its scope. Only the registry scope column can.

The registry sheet and the closure sheet

Two sheets carry the design. Reg holds one row per layer. Dates holds one column per layer. Which bodies publish those lists is set out in who publishes the state calendars.

Registry sheet layout

Layer

A: national, all bodies
B: regional
C: one office

Scope

A: named body
B: named body
C: the employer

Publisher

A: Cl_A
B: Cl_B
C: Cl_C

Range

A
B
C

Layer key	Layer name	Date column	Scope	Effective from	Effective to	Retrieved
US_FED	Federal holidays	Cl_Fed	All rows	2027-01-01	2027-12-31	2026-11-30
TX_STATE	Texas state holidays	Cl_TX	Texas rows	2027-01-01	2027-12-31	2026-11-30
TX_TRV	Travis County closings	Cl_TXTrv	County rows	2027-01-01	2027-12-31	2026-12-05
NY_STATE	New York state holidays	Cl_NY	New York rows	2027-01-01	2027-12-31	2026-11-30
CA_STATE	California state holidays	Cl_CA	California rows	2027-01-01	2027-12-31	2026-11-30

Sample values, not a published list.

Cl_Fed	Cl_TX	Cl_TXTrv	Cl_CA
2027-03-08	2027-03-08	2027-03-29	2027-03-31
2027-03-12		2027-03-30

Placeholder dates that show the shape. Add one column per layer you use.

Build it in four steps:

List only the layers a row's deadline depends on. The usual set is federal, state agency, county office, court and school district.
Paste each list into its own column, in ISO 8601 order, with a header in row 1.
Define one name per columnCl_Fed = Dates!$A$2:$A$500.
Leave the spare rows blank. The count ignores blanks, and a fixed range will not stretch.

State closure dates come from the state's human resources, comptroller or auditor office. Courts and school districts publish separately. The federal layer comes from the OPM.

Stacking ranges inside the count

The holidays argument takes an array, so several ranges fit in one call.

=NETWORKDAYS($B2,$C2,Cl_TX)
=NETWORKDAYS($B2,$C2,(Cl_TX,Cl_Fed))
=NETWORKDAYS($B2,$C2,(Cl_TX,Cl_Fed,Cl_TXTrv))

Union brackets build one reference from three columns. Wrap the union in a second pair of parentheses inside a longer formula, or Excel reads the comma as the next argument. The set behavior is covered in the layering formula.

The same argument works in WORKDAY, with a negative day count to step back.

=WORKDAY($B2,10,(Cl_TX,Cl_Fed))

Example: March 2027 with three layers

March 2027 has 23 weekdays. Let Cl_TX name 8 March 2027, Cl_Fed name 8 and 12 March, and Cl_TXTrv name 29 and 30 March.

Holidays argument

None: omitted
A only: (Cl_TX)
A and B: (Cl_TX,Cl_Fed)
B and A: (Cl_Fed,Cl_TX)
A, B and C: (Cl_TX,Cl_Fed,Cl_TXTrv)

Result

None: 23
A only: 22
A and B: 21
B and A: 21
A, B and C: 19

Weekdays removed

None: 0
A only: 1
A and B: 2
B and A: 2
A, B and C: 4

Rows three and four agree: a union has no order. The last row loses four weekdays, not five, because 8 March was already gone.

Layer counts for March 2027

23base weekdays, no layers
22layer A only
21layers A and B
19layers A, B and C

Resolving each row's layers by lookup

A row names its layers as text. MATCH finds each name in the registry, and CHOOSE returns the matching column as a range.

Row	Start	End	Slot 1	Slot 2	Slot 3	Business days	Scope note
14	2027-03-01	2027-03-31	TX_STATE	US_FED		21	Texas payroll cutoff
15	2027-03-01	2027-03-31	US_FED	TX_STATE		21	Same span, slots reversed
16	2027-03-01	2027-03-31	TX_STATE	US_FED	TX_TRV	19	County filing

=NETWORKDAYS($B2,$C2,
 (CHOOSE(MATCH($D2,Reg!$A$2:$A$6,0),Cl_Fed,Cl_TX,Cl_TXTrv,Cl_NY,Cl_CA),
  IF($E2="",Cl_None,CHOOSE(MATCH($E2,Reg!$A$2:$A$6,0),Cl_Fed,Cl_TX,Cl_TXTrv,Cl_NY,Cl_CA)),
  IF($F2="",Cl_None,CHOOSE(MATCH($F2,Reg!$A$2:$A$6,0),Cl_Fed,Cl_TX,Cl_TXTrv,Cl_NY,Cl_CA))))

Cl_None points at one empty cell, so a blank slot contributes no dates. Add a fourth IF and CHOOSE pair for a fourth slot.

Row 14 resolves twice. MATCH returns 2 for TX_STATE and 1 for US_FED, so CHOOSE returns Cl_TX and Cl_Fed. The result is 21, the same as the hand-stacked A and B call above.

Row 15 reverses the slots and still returns 21. Row 16 adds TX_TRV and drops to 19.

The same resolved slots feed WORKDAY. Ten business days forward from Friday 5 March 2027 under row 14 lands on Tuesday 23 March, against Friday 19 March with no layers. The day-stepping arithmetic is under adding business days.

XLOOKUP cannot do this step. It returns the value at the matched row, not the whole column of dates.

Checks that catch a layer applied twice

Sum the twelve monthly counts and compare with one annual call.
Count a span made only of rest days. It must return zero with every layer applied.
Put COUNTA beside each layer in the registry, so a layer that failed to load shows as zero dates.
Run ISNUMBER over each date column. A text date raises a value error in the count cell, not in the range.
Compare the annual count with that year's weekday base260, 261 or 262.

Store dates in ISO 8601 order. That ordering parses the same way under every locale and sorts correctly while it is still text.

The gap between that base and the annual count should equal the number of distinct weekdays in the union of your layers, and the base itself is derived under how a year's weekday base is worked out. If the gap does not match, a weekday is subtracted twice or not at all. That is the arithmetic form of idempotence.

A court holiday applied to a payment row gives a confident wrong number with no symptom. The scope column is the only place that error can be seen.

Common questions

Which weekend convention applies when layers differ?

Then they are not layers of one calendar. Count them separately and decide which answer governs the deadline.

Does stacking ranges slow the sheet down?

Not at these sizes. A layer holds tens of dates per year, and the function ignores anything outside the span before doing any work.

Can the registry live on the same sheet as the counts?

Yes, and it beats keeping the layer names in someone's head. What matters is that scope and retrieval date stay visible to whoever reads the number.

What if a state amends its calendar mid-year?

Add a new layer key with its own effective period and leave the old column in place. Re-run the count and check the amended span against both versions.