Spreadsheet and API tools formula testing for parity

For spreadsheet and API tools formula testing—say, Excel's NETWORKDAYS against the Nager.Date public-holiday API—a disagreement between a sheet and a service that answer the same question differently is a reconciliation problem forever. The difference is usually one, it usually lives at an endpoint, and it usually survives every test built from long spans.

The fix is a small suite of cases that both sides must pass, written once and run on both. This page is that suite and the reasoning behind each case.

What to take away

The conventions the suite pins

Before any case can have an expected value, four things are fixed and written at the top of the suite: (1) both ends of a range are counted; (2) Saturday and Sunday are rest days; (3) closures arrive as an explicit list, never inferred from a calendar lookup; and (4) every date travels as an ISO 8601 string (YYYY-MM-DD), not a bare serial number.

On the spreadsheet side, closures are a cell range fed to NETWORKDAYS(start, end, holidays) or a list passed to NETWORKDAYS.INTL. On the API side, Nager.Date returns a JSON array of holiday objects for a given country and year; the test harness extracts the date strings and feeds them as the holiday parameter. The two mechanisms must be pinned to the same list, or the parity is meaningless.

Those are choices, not facts. What matters is that they are written down where both implementations can be configured to them, which is the argument set out under spreadsheet and interface tools.

Counting between dates

All in March 2027, Saturday and Sunday closed, both ends counted.

Rows four and five are the ones that catch an endpoint mismatch, because they are the only cases where the two conventions differ by the whole answer rather than by one. Row seven has two defensible answers and no defensible silence: a service that returns 6 for it has swapped the arguments without saying so.

Row eight is the cross-check. It must equal the sum of twelve monthly counts, and the reasoning behind 261 is under counting a year.

Stepping forward and back

The last two rows are where implementations part company. A step of zero from a closed day has two reasonable answers, and a suite that does not pin one will pass on both sides and fail in production. The forward and backward mechanics are under adding business days and subtracting business days.

The type cases

These are not arithmetic. They check that the two sides agree about what a date is.

Accept a plain date without time; it must not gain a time. Accept a moment with an offset; it must not be silently read as a date. Reject a moment with no offset instead of assuming one, and reject a date sent as a bare serial number because its meaning depends on a workbook setting that does not travel—Excel for Windows counts from the 1900 epoch (serial 1 = 1 January 1900) while legacy Mac workbooks use the 1904 epoch, a 1,462-day gap.

That last case is the one people argue about, and the argument settles quickly once someone copies values between two workbooks with different epochs.

A serial is a continuous day count like the astronomical Julian day number. It means nothing until you say where it starts. Text in ISO 8601 order carries its own starting point, which is why the contract asks for it.

Running it

Keep the cases as rows, with the inputs, the convention flags, the expected value, and one column per implementation. Then the suite is itself a sheet, the comparison approach can be applied to the two output columns, and a disagreement is visible rather than reported.

Suppose the sheet column returns 6 for 'Ordinary week' and the API column returns 5. The difference is one day at the right endpoint: the service excluded the end date while NETWORKDAYS includes both. Pinning convention (1) above and re-running confirms the fix. That single-row disagreement, caught at the boundary, is the whole point of the suite.

Re-run it whenever either side changes, whenever the closure list changes, and whenever someone widens a window or adds a calendar. This is ordinary regression testing applied to a data contract instead of to code, and the failures it catches are exactly the ones that otherwise appear as a rounding disagreement in a report.

The hour-level version of the same suite, where the cases are windows rather than days, is under working hours.

Common questions

How many cases are enough?

Sixteen or so, if they are chosen at the boundaries. A hundred long spans prove less than the two single-day cases above.

What if the two sides genuinely have to differ?

Then record the difference as a rule with a conversion, and test the conversion. What cannot stand is an undocumented difference discovered during a reconciliation.

Should the suite include closures?

A few cases, yes, including one where a closure falls at a weekend and must have no effect. Most cases should be closure-free so that a failure points at the arithmetic rather than at the list.

Where do the expected values come from?

Derive them by hand from the span's length and start day, then check them against a day-by-day sweep. Taking them from one of the two implementations makes the suite a description of that implementation rather than a test of it.