Store calendar dates as dates and event moments as timestamps with a known timezone or offset. Convert reporting boundaries deliberately, then verify edge cases instead of applying an unexplained fixed-hour correction.
In this guide
What belongs in a date-normalization record?
The rows illustrate three different data contracts. A date-only field must not become the previous day because a formatter treated it as UTC midnight. For the timestamp row, retain the original instant and named zone with the display value. For the ambiguous slash date, ask the source owner rather than choosing the format that happens to parse.
- Inventory each date column with its meaning, source format, timezone or offset, and the report that uses it.
- Keep the original string and add a separate interpreted field during testing.
- Parse date-only and timestamp fields under their own rules. Leave ambiguous formats unresolved rather than guessing day/month order.
- Convert event moments to the selected reporting timezone before assigning them to days or weeks.
- Check values near midnight and any daylight-saving transition relevant to the dataset.
- Record the date range, timezone, completeness status, and extraction time with the report before comparing totals.
| Source value | Known meaning | Normalized result |
|---|---|---|
| 2026-10-20 | Calendar date only | 2026-10-20; do not shift by timezone |
| 2026-10-20T00:30:00Z | UTC instant | 2026-10-19 17:30 in America/Los_Angeles |
| 03/04/2026 | Locale not supplied | Hold for month/day convention |
Compare reports after their boundaries match
Two exports can use the same printed date range while including different event moments. Confirm source timezone, selected filters, and whether the final day is complete. Preserve unknown or unavailable periods rather than making them look like zero activity.
A reported change in sales or sessions becomes easier to interpret after those boundaries are fixed. Keep this calibration separate from commentary about why the business changed. The same discipline applies when a daily figure arrives late and is later revised.
A day and an instant need different treatment
A fixed adjustment can appear to repair one row and break another when the source convention or seasonal offset differs. Diagnose the meaning and conversion step that produced the discrepancy before changing data.
| Source | Meaning | Review action |
|---|---|---|
| 2026-10-20 | Calendar renewal day | Keep the calendar day without a timezone conversion. |
| 2026-10-20T01:00:00Z | An event instant in UTC | Assign to a reporting day after conversion to the selected timezone. |
| 04/05/2026 | Source convention unknown | Ask whether day or month comes first before parsing. |
Name what the date field actually means
A contract renewal day, a warehouse dispatch timestamp, and a report's last complete day are different data concepts. A date-only value such as 2026-10-20 does not inherently mean midnight in a chosen timezone. A timestamp describes an instant only when its offset or source timezone is known.
Grist distinguishes Date and DateTime fields. Use that distinction as a design prompt for any table or spreadsheet: preserve the business meaning instead of letting an importer choose it from the characters alone. Keep ambiguous source formats as text until the source convention is confirmed.
Ask for an ambiguity audit before a conversion
Audit these date fields before conversion. FIELD MEANINGS: [calendar day or event moment] SOURCE FORMATS AND TIMEZONES: [known values or unknown] REPORTING TIMEZONE AND WINDOW: [state] SAMPLE VALUES: [include boundary cases] Identify ambiguous formats, missing offsets, date-only fields at risk of unwanted conversion, and incomplete reporting days. Propose interpretation rules and questions. Preserve source strings. Return a conversion plan and test cases; do not guess an unknown timezone or modify the source file.
Keep a readable date contract in the handoff
Document the field meaning, conversion rule, and boundary examples beside the output. A colleague should be able to explain why a source timestamp belongs to a reporting day without reverse-engineering a formula from the final spreadsheet.