Costs
How file settings quietly change spreadsheet and API answers in Sheets
Spreadsheet and API tools in Google Sheets: the file settings that change every answer, the epoch it counts from, and what happens at an import boundary.
Two settings decide whether a sheet full of dates means anything: the file's timezone and its locale. Both are inherited from whoever created the file, both are invisible in every cell, and both change answers rather than raising errors.
What to take away
- The file carries a timezone. Everything derived from the current moment resolves in it, so a file made in one region and used in another is offset by a fixed amount everywhere at once.
- The file carries a locale, and it decides how a text date is parsed. The same string can become two different dates in two files.
- A typed date is a plain number with no zone. That is the right behavior for a window definition and the wrong behavior for an event log.
The two settings
Set both deliberately and write them into a cell where a reader can see them. A file that inherits its creator's settings is a file whose numbers cannot be interpreted without knowing who made it.
| Setting | What it changes | Symptom when wrong |
|---|
Two file settings and their symptoms
Timezone
- What it changes
- Current-moment resolution
- Symptom when wrong
- Uniform timestamp offset
- Scope of damage
- Every timestamp
- Rows affected
- All
Locale
- What it changes
- Text parsed as date
- Symptom when wrong
- Day and month swapped
- Scope of damage
- Only ambiguous dates
- Rows affected
- About one third
The locale symptom is the nastier one, because it is selective. Dates with a component above twelve are unambiguous and parse correctly, so a column can be right for two thirds of its rows and silently transposed for the rest.
The epoch
Sheets counts days from a zero point near the end of 1899, so a date is a whole number and a timestamp is that number plus a fraction of a day. The arithmetic is the same as any other spreadsheet: subtract two values for whole days, multiply the difference by 24 for hours.
Where serials stop being interchangeable
- 1899Sheets day-count zero point
- Early 1900Some tools keep a day that never existed
- March 1900 onwardSerials safe to treat as interchangeable
- BoundaryTest it rather than reason about it
Assuming two tools agree at the very bottom of the range is not safe. Some products carry a documented defect for early 1900, keeping a day that never existed, while others do not.
That is the general leap year problem in its most familiar form. Treat serials as interchangeable only from March 1900 onward, and test the boundary rather than reasoning about it.
The practical consequence is simple. Move dates between tools as text in ISO 8601 order rather than as serials. Text carries its own meaning; a serial carries a meaning that lives in the file it came from.
The import boundary
Data arriving from elsewhere lands as text unless something converts it. The conversion happens under the file's locale, which is where the selective transposition above comes from.
Three habits remove most of it. Insist on ISO order at the source, which parses the same way under every locale. Convert once, in a dedicated column, rather than relying on the display. And check the converted column is numeric rather than reading it, because a text date and a real date look identical in most formats.
A closure range is where this bites hardest. One text entry in a range of dates does not fail on import; it fails the first time a counting function reaches it, which can be a month later and in a different sheet. Where those ranges come from and how they should be held is under how closure calendars are made.
Typed dates carry no zone
A date or a time typed into a cell is a bare number. It never shifts when the file's timezone changes, and reading it as a moment requires a zone that the sheet does not hold.
For a window definition that is exactly right: 09:00 means the schedule's nine, and the schedule's zone lives beside it rather than inside it. For an event log it is wrong, because two events logged in two regions become indistinguishable. Log moments with an explicit offset, as text, and convert on the way in.
What a zone actually is, as opposed to an offset, is set out under time zone. The distinction matters because a fixed offset stored in place of a zone is right for part of the year and an hour out for the rest.
Crossing into an interface
The boundary contract has three parts: a plain date is text in ISO order, a moment is text with an offset, and neither is ever a bare serial. The counting convention has to cross too, because spreadsheet counting functions include both endpoints and many interfaces do not.
That difference of one survives testing on long spans and appears only at the ends, which is why the parity tests are worth writing before anything is integrated. The contract is under spreadsheet and interface tools, the endpoint conventions under counting between two dates, and the hour-level arithmetic that shares the same fraction-of-a-day representation is under working hours.
Common questions
Where should the file's timezone and locale be recorded?
In a cell near the top of the sheet, as text. The settings dialog is authoritative and nobody opens it; a cell is read by everyone who reads the numbers.
Why do some of my imported dates transpose the day and month?
Because only the ambiguous ones can. A component above twelve is unambiguous and parses correctly, which is why the problem looks intermittent.
Should I ever send a serial number to an interface?
No. It has no meaning outside the file that produced it, and there is no field on the wire that carries the epoch.
Does the file's timezone affect a difference between two typed dates?
No. Both are bare numbers and the difference is exact. It affects anything resolved from the current moment.
