Practical diagnosis

Why identical displayed dates do not match

Reviewed 8 October 2026 · Synthetic examples

Two cells can both show January 1, 2026 yet compare unequal. A date-only format may conceal a time fraction. It does not erase it. Before blaming a lookup, inspect the underlying numbers and decide whether your task needs a whole day or an exact date-time.

Reproduce a hidden-time mismatch In a 1900-system Excel workbook or Google Sheets, enter =DATE(2026,1,1) in A1 and =DATE(2026,1,1)+0.5 in A2. Display both as dates only. They represent the same calendar day in this example, but =A1=A2 is expected to return FALSE: the values are 46023 and 46023.5. Half a day is twelve hours.

Display the second cell with a date-and-time format to expose noon. For these positive serials, =INT(A2) returns 46023 and =A2-INT(A2) returns 0.5. The first helper retains the day; the second isolates the fraction. Keep A2 unchanged so that the original time remains recoverable. INT rounds toward negative infinity, so this recipe is not a general rule for negative historical values.

Decide what equality means A daily attendance list might intentionally compare day helpers. A log of individual events should retain times. In a report, state whether “same date” means the source's calendar date or the date in a chosen reporting time zone. Dropping the fractional part first can place an event into the wrong reporting day if a zone conversion was needed.

For a synthetic fixed-offset example, 2026-01-01T00:30:00+02:00 and 2025-12-31T22:30:00Z describe the same instant. Their written calendar dates differ. This is arithmetic for the specified +02:00 offset, not a rule for a named city or daylight-saving schedule.

Preserve context beside the number A naked serial does not record the original UTC offset or named zone. Keep the original timestamp text and a separate field describing its zone convention. Do not append Z to an unqualified local time: that would assert UTC rather than convert to it.

Sheets exposes a spreadsheet time-zone setting. Power Query has a distinct Date/Time/Timezone type as well as Date/Time and Date. Neither fact establishes that every CSV timestamp will be parsed or converted as intended. Test the exact input and destination path before importing a production log. These notes intentionally do not promise automatic ISO timestamp parsing or cross-provider time-zone fidelity.