Spreadsheet and API tools holidays 2027: criteria, no dates

No closure dates are given on this page. For spreadsheet and API tools holidays 2027, a list copied from here would reach your sheet with no publisher, no scope and no retrieval date, which are the three fields that make a count defensible later.

What follows is how a single closure list gets into two tools without becoming two lists, and what has to travel with it.

What to take away

The shape of the record

Whatever holds the list, the fields are the same.

The version field is what turns a list into something a service can pin to and a sheet can label. Without it an update is an edit, and an edit cannot be rolled back or reasoned about. The discipline is the ordinary one described under software versioning.

Putting the list into a spreadsheet

Import as text in ISO 8601 order and convert once, in a dedicated column. That ordering parses the same way under every locale, which removes the class of failure where a column is correct for two thirds of its rows and transposed for the rest.

Then check that the converted column is numeric. A text date in a closure range does not fail on import; it fails the first time a counting function reaches it, which can be weeks later and in a different sheet.

In Excel and Sheets that function is NETWORKDAYS.INTL, which takes a start date, an end date and a range of holiday dates.

Keep the version and the retrieval date in cells beside the range, not in a comment. Whoever reads the count needs to see them without opening anything.

Putting the list into an API

Send dates as text, never as spreadsheet serial numbers. A serial's meaning depends on the workbook's epoch, and no field on the wire carries the epoch, so a serial arrives as a number with no defined date. The wider boundary contract is under spreadsheet and interface tools.

Have the service accept a calendar identifier with a version and echo both in its response. Then a stored answer records which list produced it, and a disagreement between the sheet and the service becomes a comparison of two version strings rather than a comparison of two totals.

Public holidays services such as Nager.Date and Calendarific take a country code and a year and return one object per closure, each with a date field. Where the provider keeps no version of its own, a parameter such as calendarId carrying a version suffix keeps the pinning on your side.

Where the closure list comes from

The underlying days are usually fixed by an enacting instrument rather than by custom, which makes them auditable. The United States codifies its federal set in statute at 5 U.S.C. 6103.

Read the instrument rather than a summary. There you learn whether a closure that lands on a weekend is substituted onto a working day, and who may add a day during the year.

Which lists apply to your obligation is a separate question and the harder one. A layer that binds courts does not necessarily bind banks. The reasoning is under how closure calendars are made and, for the sub-national layer, state and regional calendars.

The short answer on which list binds you: the instrument that creates the obligation names the calendar. A federal filing follows the federal list, a state filing follows the state list, and a private contract follows the calendar it names.

If the instrument names nothing, ask the office that receives the filing. That office counts against its own working calendar, and that is the list to use.

Two properties that make sharing work

A date in a closure range that falls on a rest day is ignored, because rest days are removed before the list is applied. A date outside the span being counted is ignored too. Together they mean one multi-year, multi-layer list can be pointed at from every calculation without being trimmed per month or per calendar.

What they do not excuse is merging several calendars into one range. That subtracts every calendar's closures from every count, and the result is low in a way nobody can attribute. Keep a calendar column and filter.

Checking that both tools agree

Count the same span in both with the same list version. Then count a span made entirely of rest days, which must be zero in both.

Then count a single open day, which must be one in both, because that is the case where an endpoint convention shows up as the whole answer rather than as a difference of one.

Worked example. Take 2027-07-01 to 2027-09-30, a quarter of 66 weekdays. Say list version 2027.1 carries two weekday entries inside it. The NETWORKDAYS.INTL formula and the service response must both return 64. For 2027 the weekday base is 261, because the year starts and ends on a Friday.

A span of 2027-07-03 to 2027-07-04 is a Saturday and a Sunday, so both tools must return 0. The single day 2027-07-05 is a Monday and must return 1 in both.

Finally compare an annual count against the year's weekday base, which is 260, 261 or 262 and is derived under counting a year. The difference should equal the number of weekday entries in the list, and if it does not, something is being subtracted twice or not at all.

Common questions

Can the sheet be the source of truth?

It can, if it is the only copy and it carries the publisher and version fields. What fails is a sheet that was seeded from a service and then edited.

How often should the list be re-retrieved?

Before any figure that will be relied on, and on a schedule otherwise. Lists gain entries during the year, and an annual figure computed in January can be stale by autumn.

Does a closure that falls at a weekend belong in the list?

Keep it, because it records what the publisher said. It has no effect on the count unless a substitution rule moves it, and the substituted date belongs in the list as well.