Spreadsheet and calendar dates differ by a day: how do you check timezones and date serials?

Separate date-only fields from timed events

This applies to calendar imports a day early, serials producing unexpected times and regional display differences. Preserve source fields, display formats and destination semantics and distinguish all-day dates from precise times. A date-looking cell does not establish storage type or included timezone.

Inspect date systems, locale formats and timezones

Inspect workbook date system and serial origin and locale-dependent text parsing. Check whether timezone-naive values became UTC or were converted twice. Excel dates generally lack timezone information and cannot automatically preserve absolute instants; define the region externally or store an offset-bearing representation separately.

Convert according to field meaning and preserve originals

Convert by business meaning: retain calendar dates for date-only fields and explicit regional instants for timed events. Validate serial and parsing rules on copies and retain originals. Do not globally shift dates or turn all values into semantically undefined strings. Check distinct destination fields for all-day and timed calendar events before writing.

Test round trips around midnight and daylight-saving transitions

Test midnight boundaries, multiple regions and daylight-saving transitions and read exports back against intended dates and times. Include date-only values and verify they do not drift. Acceptance requires correct semantics and destination display, not a serial converting to some plausible date. Correctness on the author’s machine does not establish regional consistency.

This article wrote no real calendars or private sheets and gives no universal serial origin. Platforms and old workbooks can follow different rules. Without a source timezone, absolute time cannot be reliably recovered and must remain unconfirmed. Historical dates and nonstandard calendars require additional capability checks.