appscript.dev
Guide Intermediate

Handle dates and time zones without bugs

A reliable approach to date math in Northwind scripts — TZ-aware comparisons and formatting.

Published Aug 8, 2025

Dates are the single richest source of subtle, hard-to-reproduce bugs in Apps Script. A script works perfectly for months, then quietly produces wrong results for one week in spring and one in autumn — the daylight-saving switchovers. Or it works for you and not for a colleague in another country.

The root cause is almost always an implicit assumption about which time zone a date is in. Get explicit about time zones everywhere — in formatting, in comparisons, in arithmetic — and the whole class of bugs disappears.

The trap

A JavaScript Date is just a number: milliseconds since the Unix epoch, in UTC. It carries no time zone of its own. The time zone only enters the picture when you display or format that number.

This causes three recurring problems:

  • new Date() is UTC under the hood. The instant is fine, but any method that splits it into year/month/day/hour does so in some assumed zone.
  • Display is environment-dependent. The same Date can render differently depending on the script’s time zone setting, the spreadsheet’s setting, or a viewer’s browser.
  • Comparisons silently drift across DST. Naive day arithmetic that adds a fixed number of milliseconds breaks on the two days a year when a local day is not 24 hours long.

Always specify TZ

Never let a date format itself with whatever default is in scope. Use Utilities.formatDate() and pass the time zone explicitly, every time. This makes the output deterministic regardless of where the script runs.

// Today's date as a string, explicitly in the London time zone.
const today = Utilities.formatDate(new Date(), 'Europe/London', 'yyyy-MM-dd');

// Start-of-day timestamp, again pinned to a named zone — not a guess.
const start = Utilities.formatDate(
  new Date(), 'Europe/London', "yyyy-MM-dd'T'00:00:00"
);

Use IANA zone names like Europe/London or America/New_York, never raw offsets like GMT+1. An offset is wrong half the year; a named zone knows when DST applies and adjusts itself.

For comparisons

When you need to compare two dates, the safest approach is to compare ISO 8601 strings. As long as both strings include the same offset (or both are UTC), a plain string comparison gives the correct chronological order.

// toISOString() always returns UTC with a 'Z' suffix — a stable,
// unambiguous representation that sorts and compares correctly.
const aIso = a.toISOString();
const bIso = b.toISOString();

// Lexical string comparison matches chronological order here, because
// ISO 8601 is designed so that earlier instants sort earlier.
return aIso < bIso;

This works because ISO 8601’s fixed-width fields (year, then month, then day, and so on) mean alphabetical order equals time order. It sidesteps every time zone subtlety as long as both sides are in the same reference frame.

DST gotchas

Daylight-saving transitions are where most date bugs actually bite. Keep these in mind.

  • setHours(0, 0, 0, 0) does not always give local midnight. It sets the fields in the runtime’s assumed zone, which may not be the zone you care about — and on a DST-transition day, “midnight” may not even exist.
  • Adding 86400000 ms is not “+1 day”. On the day a clock springs forward or falls back, the local day is 23 or 25 hours long. Adding a fixed millisecond count lands you an hour off.
  • For day arithmetic, mutate the date fields instead. `d.setDate(d.getDate()
    • 1)` adds one calendar day correctly across DST boundaries, because it works in calendar terms rather than raw milliseconds.
// Correct: advances by one calendar day, DST-safe.
const tomorrow = new Date(today);
tomorrow.setDate(tomorrow.getDate() + 1);

// Wrong: drifts by an hour on DST-switch days.
// const tomorrow = new Date(today.getTime() + 86400000);

Time zones in Sheets

A spreadsheet has its own time zone setting, separate from the script’s. You will find it under File → Settings. Every Date value stored in a cell renders according to that setting, not the script’s.

This is a frequent source of “the script wrote the right value but the cell shows the wrong time” confusion. When a script writes dates into a sheet, make sure the spreadsheet’s zone matches what you intend, and use that same zone in any Utilities.formatDate() calls that produce strings for that sheet.

Common mistakes

  • Formatting without a zone. date.toString() or a bare formatDate() uses whatever default is in scope, so the same code gives different output in different projects. Always pass an explicit zone.
  • Using offsets instead of named zones. GMT+1 is correct for only part of the year. Europe/London is correct all year.
  • Adding milliseconds for day math. Fixed-millisecond arithmetic breaks on DST days. Use setDate() for calendar-day changes.
  • Comparing Date objects across zones without normalising. If the two dates were built in different zones, convert both to UTC ISO strings first.
  • Forgetting the spreadsheet’s own zone. A script can be perfectly correct and still display the wrong time because the sheet’s setting differs. Check File → Settings when cell output looks off.
  • Assuming the user’s zone equals the script’s zone. A script shared across countries will surprise you. Pick one canonical zone and be explicit about it everywhere.
↑ All guides