Dates, Time Zones and Deadlines: The Data Traps Spreadsheets Hide

Your Toronto office logs a contract as starting 03/04/2026. Your London office reads the same cell and books the kickoff for the third of April. Toronto meant the fourth of March. Nobody notices for a month, because the cell looked perfectly reasonable to both of them.

Dates feel like the simplest data you own. They are actually where spreadsheets hide the most ambiguity, because a sheet will happily display something that looks like a date while storing something else entirely. When you design the new tool, date and time fields deserve more thought than almost anything else.

This post is about design, not cleanup. If you’re still fixing the existing mess, Cleaning Your Spreadsheet Data Before Migration: A Practical Guide covers that. Here we’re deciding how the new system should hold dates so the mess doesn’t come back.

A date is not text that looks like a date

Under the hood, your spreadsheet tool stores a real date as a number: a count of days from a fixed starting point. But the same column can also contain text that merely resembles a date, typed with a leading apostrophe, pasted from an email, or imported from a file the sheet didn’t recognise.

Text dates sort alphabetically, so “10/01/2026” lands before “9/30/2025”. They don’t filter by range. They silently break every “days overdue” formula that touches them.

In your new tool, every date must live in a proper date field. No exceptions for “notes about timing”. If people need to say “sometime in Q3”, that’s a separate free-text field next to a real target date, not a replacement for it.

Pick one unambiguous format for anything that leaves the tool

Inside the tool, a date field is just a date; each person can see it displayed in their own local style. The danger is at the edges: exports, imports, emails, file names and integrations.

For anything machine-readable, use the international standard format: year, month, day, with hyphens. So 4 March 2026 becomes 2026-03-04. It has three big advantages:

• It can’t be misread as month-first or day-first, because nobody writes year-day-month.
• It sorts correctly even as plain text.
• Almost every system accepts it without configuration.

For anything human-readable where you can’t control the display, spell the month out: “4 Mar 2026”. Never send “03/04/26” anywhere that matters.

Decide what kind of “when” each field is

Most date confusion comes from treating different kinds of time as one thing. Before you create a field, decide which of these it is:

The first row trips people up most. If you store a contract start date as “midnight on 4 March” with a time zone attached, a colleague several hours behind may see it as 3 March. Date-only facts should be date-only fields. Most internal tool platforms offer a “date without time” option; use it.

Time zones: store one, show many

A “moment in time” is the same instant everywhere on Earth. The clean way to handle it is to store it in UTC (Coordinated Universal Time, the reference clock with no daylight saving) and convert to each viewer’s local time on display. Good platforms do this for you; check that yours does, and check what time zone automations and reports run in, because that’s often a single account-level setting.

Daylight saving is where hand-rolled approaches fall apart. Say your team in New York and your team in London have a standing 14:00 London handover. Most of the year that’s 9:00 in New York. But North America moves its clocks on the second Sunday in March and the first Sunday in November, while Europe moves on the last Sundays of March and October. For two or three weeks in spring and about a week in autumn, the gap is four hours, not five. If your sheet has a column that says “London time minus 5”, it is wrong twice a year.

That’s also why future appointments should store the named time zone (“America/Edmonton”), not a fixed offset like “UTC−7”. The name knows about daylight saving and survives rule changes; the offset is a snapshot that goes stale.

Deadlines need definitions, not just dates

“Due Friday” sounds precise until three people interpret it. Does it mean the start of Friday, close of business Friday, or midnight Friday? Whose Friday, if the requester is in Vancouver and the team is in Halifax?

Write the definition down once and build it into the tool:

• Cut-off time. “Due date means 17:00 in the team’s home time zone.” Then store the deadline as a timestamp, not a bare date.
• Business vs. calendar days. “Five days” for a purchase approval almost always means business days. Decide which, and whether public holidays count. If holidays matter, keep a small holiday table rather than trusting everyone’s memory.
• Inclusive or exclusive. Does “valid until 31 March” include 31 March? Pick one meaning for the whole company. “Valid through” is clearer than “valid until” if you want inclusive.
• Clock start. Does a turnaround target start when a request is submitted, or when it’s accepted? Store both timestamps so you can measure either. Service Levels for Internal Processes: Setting and Tracking Turnaround Times goes further on this.

A fictional example: a 40-person agency had “deadline” columns on four tabs, each meaning something different. Three clearly named fields (“Internal review due”, “Client delivery due”, “Promised to client”) ended a recurring argument nobody had realised was about definitions.

Don’t let calculated dates drift

Spreadsheets often compute a renewal date with a formula, then someone pastes values over the top, and the “calculation” quietly becomes a typed number. In your tool, decide whether each derived date is calculated (renewal = start date + term) or entered and then locked. Don’t allow both. Calculated Fields: What to Move Into Your Tool and What to Leave Behind covers how to choose.

Also watch “today”-based calculations. “Days overdue” depends on whose today, and a report run at 23:00 in one office is already tomorrow in another. Fix the reference time zone for reports.

Where to start this week

1. List every date-like column in the spreadsheet you’re migrating and label each one: calendar date, moment in time, future appointment, or duration.
2. Write a one-paragraph deadline definition (cut-off time, time zone, business or calendar days, inclusive or exclusive) and get the process owner to sign it off.
3. Check your platform’s settings for the account time zone and the time zone used by automations and reports.
4. Change any export or integration that sends dates to use the year-month-day format.