Spreadsheet Date and Workday Counting Mistakes

Almost none of the spreadsheet date bugs you will hit are arithmetic bugs. They are data bugs: a value that is not the type you think, or an instant that got re-interpreted on its way between two systems. The arithmetic then runs perfectly on the wrong input and returns a confident answer.

Two kinds, split by what they damage.

Errors that corrupt the data

These break the values themselves, usually on the way in from somewhere else, and they survive every subsequent calculation.

Text that looks like a date

A column of dates where some entries are text. They render identically. They sort alphabetically rather than chronologically, they are excluded from every date function, and in a holidays range they are simply ignored: no error, just closures that quietly stop being observed.

The tell: by default a real date right-aligns and text left-aligns. Select the column and check.

Where it comes from: almost always an import, a paste from a web page, or a leading apostrophe someone typed years ago.

The first twelve days of every month

03/04 is the third of April or the fourth of March, depending on the locale reading it. Both are valid, so an import that gets it wrong raises nothing.

The vicious part is which rows survive. A day above the twelfth cannot be a month, so it either fails loudly or comes through correctly. Days one to twelve pass through silently and wrong. Your corrupted rows are concentrated in the first fortnight of each month, and they look exactly like the good ones.

Fix: move dates between systems in an unambiguous format (year first, four digits, hyphen-separated), and never rely on the receiving system's locale.

Two-digit years

Which century a two-digit year means is a configurable pivot, not a fact. Two systems can disagree by a hundred years and both be behaving as documented.

Fix: four digits everywhere, always, including in test data.

The midnight shift

A date-only value is given a time of midnight, converted to another zone, and lands on the previous day. Nothing was corrupted in any obvious way: the pipeline did exactly what it was told.

This is the single most common source of "everything is one day out" reports, and it is worse than it looks because it affects only some rows: those where the conversion crosses midnight, which depends on the offset, which depends on the time of year.

Fix: decide whether each field is a date or an instant, and never convert a date. A birthday, a filing deadline and a holiday entry are dates; they have no zone and must not be given one. A timestamp is an instant; it must always carry an offset.

Serial numbers escaping

Spreadsheets store dates as a number counted from an epoch. Copy such a column as values into a system expecting text and you get five-digit integers. Worse, the epoch itself is a workbook setting in Excel: change it and every date in the file shifts.

Fix: export formatted values, not underlying values, when the destination is not a spreadsheet. Check the epoch setting before trusting any workbook you did not create.

Autocorrect making dates out of things that are not dates

Reference codes, version numbers and part numbers containing separators get helpfully converted. The original string is gone.

Fix: format the column as text before pasting, not after. Afterwards is too late, the conversion is not reversible from the converted value.

Errors that corrupt the answer

Here the data is fine. The conventions on either side of a boundary disagree, and the arithmetic faithfully computes the wrong thing.

A timestamp with no offset

Three things look similar and behave differently:

The middle one is the dangerous one, because it carries the authority of a full timestamp and none of the information. Every consumer will assume a different zone, and each will be internally consistent.

Fix: an API returning a deadline should return an instant with an offset, or a bare date, and should never return the middle form. If you receive one, find out what zone it is in before you use it, and store the answer.

Off-by-one between a sheet and a service

A spreadsheet's between-dates function typically counts both endpoints. Most services count neither, or one. Reconcile the two and you get a consistent difference of one, which someone eventually patches with a hard-coded adjustment.

That adjustment is correct until an input changes shape (a weekend start, a zero-length interval, a count in the other direction), and then it is wrong in a way nobody can find, because the bug is a subtraction sitting three files away from the arithmetic.

Fix: never patch a convention mismatch with an offset. Make both sides state their convention and convert explicitly, once, where the boundary is.

A holiday range that does not cover the window

Closure lists are built forwards. Then someone counts backwards, or extends a count into the following year, and reaches past the end of the list. Dates outside the range are treated as ordinary working days, silently.

Fix: validate that the range spans the whole window before calculating, in both directions, and fail loudly if it does not.

Closure dates hard-coded in a formula

They survive every copy of the workbook and are never revisited. Two years later the sheet is still skipping days that have moved and observing days that have not existed for a while.

Fix: closures live in a table, with a source and an as-of date in a cell somebody can see. Which list to use and where to get it is a separate decision, and a more important one.

Checks for spreadsheet date and workday-count mistakes

Three cheap checks find nearly everything above:

Use these checks wherever a spreadsheet feeds a workday count or a date-based deadline.

Common questions

Why do only some imported dates transpose the day and month?

Because only the ambiguous ones can. Any date with a component above twelve is unambiguous and parses correctly, so a column can be right for two thirds of its rows and silently wrong for the rest. Importing in ISO 8601 order removes the whole class.

Is a serial number ever safe to send to another system?

No. Its meaning depends on the origin the file counts from, and no field on the wire carries that origin. The problem is the same one that makes an epoch meaningless without being named. For broader handoff context, see spreadsheet and API tools.

How do I find a text date hiding in a date column?

With a formula, not with your eyes. A type column that flags any non-numeric value costs nothing and catches imports on the day they arrive.

Why does the same span give a different count in the sheet and the service?

Almost always an endpoint convention. Spreadsheet counting functions include both ends; many interfaces do not. Test a one-day span, where the two conventions differ by the whole answer rather than by one.

Which check finds the most problems for the least effort?

The same canary can check a working-days-per-month count: compare the result with a known calendar and endpoint convention.