Can I use 'UTC+5:30' instead of 'Asia/Kolkata' as the timezone string?

No. Utilities.formatDate requires a valid IANA timezone name. Offset strings like 'UTC+5:30' or 'GMT-8' will throw an error or silently fall back to UTC depending on the runtime version. Use 'Asia/Kolkata', 'America/Los_Angeles', etc. The full list is at en.wikipedia.org/wiki/List_of_tz_database_time_zones.

The output is 12 hours wrong — what happened?

Almost always an 'hh' vs 'HH' mismatch in the format string. 'hh' is 12-hour clock (1-12); 'HH' is 24-hour clock (0-23). If you use 'hh' without an 'a' (AM/PM) token, times after noon are indistinguishable from morning times. Switch to 'HH' for unambiguous output.

How do I get the spreadsheet's own timezone to use as a default?

Call SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(). It returns the IANA name set in File > Settings, so you can pass it directly into Utilities.formatDate without hard-coding a zone name.

Does this work with the EPOCHTODATE or TEXT formula in a cell instead of Apps Script?

Not directly. The built-in TEXT formula and date formats in Sheets always render in the spreadsheet's display timezone — there is no formula argument to specify a different target zone. If you need a cross-timezone display in a cell formula rather than a script, the only option is a custom Apps Script function called as =CONVERT_TZ(A2, 'UTC', 'America/New_York') using the same Utilities.formatDate approach.

// Sheets · Apps Script

Convert a date between timezones (with DST) in Google Sheets.

Use Utilities.formatDate with an IANA timezone string to convert dates between timezones in Google Apps Script — DST-aware, no manual offset math required.

“I have a timestamp in one timezone and need to display it correctly in another, without my formula breaking twice a year when clocks change.”

The script

copy · paste · trigger

convertTimezone.gs

Apps Script

// Convert a date value from one IANA timezone to another.
// Returns a formatted string in the target zone, DST-aware.
function convertTimezone(dateValue, fromZone, toZone) {
  var date = new Date(dateValue);
  var fmt = 'yyyy-MM-dd HH:mm:ss';

  // Utilities.formatDate always interprets the Date in the zone you pass.
  // IANA names like 'America/New_York' honour DST; '+05:00' strings do not.
  var formatted = Utilities.formatDate(date, toZone, fmt);
  return formatted;
}

// Example: stamp a sheet cell with the UTC equivalent of a local entry.
function stampUtc() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var raw = sheet.getRange('A2').getValue(); // expects a Date cell
  var utc = convertTimezone(raw, 'America/Chicago', 'UTC');
  sheet.getRange('B2').setValue(utc);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why a fixed offset breaks and IANA names do not

The naive approach is to add or subtract hours: take a UTC timestamp, add -5 for Eastern time, done. It works until March and November, when the US switches between EST (UTC-5) and EDT (UTC-4). At that point every value is off by an hour and the error is silent — no formula error, just wrong data.

Utilities.formatDate accepts any IANA timezone string (the ones in the tz database: 'America/New_York', 'Europe/Berlin', 'Asia/Kolkata') as its second argument. Google's runtime knows the full DST transition history for each zone and applies the correct offset for the exact moment represented by the Date object. You never touch the offset math yourself.

The first time I hit a DST bug in a Sheets automation it had been silently mis-stamping meeting exports for six weeks. Switching the hard-coded '-05:00' to 'America/Chicago' fixed it in one character swap.

How Apps Script sees a date from a cell

When you call range.getValue() on a cell formatted as a date or datetime, Apps Script returns a JavaScript Date object. Internally that object is always UTC milliseconds since epoch — the spreadsheet display timezone is cosmetic, not stored in the value.

Utilities.formatDate(date, zone, format) takes that UTC-grounded object and renders it as a string using the wall-clock time in whatever zone you pass. The format string uses Java SimpleDateFormat tokens: 'yyyy' for four-digit year, 'MM' for month, 'HH' for 24-hour hour, 'mm' for minutes, 'ss' for seconds. Case matters — 'MM' is month, 'mm' is minutes.

If you then write that string back to the sheet with setValue(), Sheets will store it as a string, not a Date. That is usually fine for logs or display columns. If you need a real Date object back, parse it with new Date(formatted) — but at that point the timezone context is lost again, so keep a separate 'zone' column if the destination matters.

Wiring it to a spreadsheet column

The stampUtc() function in the snippet shows the minimal wiring: read one cell, convert, write the result next to it. For a whole column, wrap the same logic in a for loop over getValues() and write back with setValues() — one batch read and one batch write avoids the per-cell quota hit that comes from calling getValue() in a loop.

If the source timezone varies per row (say, users in different regions submit a form), store the zone name in its own column (column C as 'America/Los_Angeles', column D as 'Europe/London') and pass row[2] and row[3] directly into convertTimezone(). The function does not care where the zone string comes from.

The spreadsheet's own timezone (File > Settings > Time zone) affects how Sheets displays Date values on screen, but it does not affect what getValue() returns or what Utilities.formatDate outputs. Keep that setting consistent anyway — mixed-zone spreadsheets confuse collaborators even when the data is correct.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Can I use 'UTC+5:30' instead of 'Asia/Kolkata' as the timezone string?: No. Utilities.formatDate requires a valid IANA timezone name. Offset strings like 'UTC+5:30' or 'GMT-8' will throw an error or silently fall back to UTC depending on the runtime version. Use 'Asia/Kolkata', 'America/Los_Angeles', etc. The full list is at en.wikipedia.org/wiki/List_of_tz_database_time_zones.
The output is 12 hours wrong — what happened?: Almost always an 'hh' vs 'HH' mismatch in the format string. 'hh' is 12-hour clock (1-12); 'HH' is 24-hour clock (0-23). If you use 'hh' without an 'a' (AM/PM) token, times after noon are indistinguishable from morning times. Switch to 'HH' for unambiguous output.
How do I get the spreadsheet's own timezone to use as a default?: Call SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(). It returns the IANA name set in File > Settings, so you can pass it directly into Utilities.formatDate without hard-coding a zone name.
Does this work with the EPOCHTODATE or TEXT formula in a cell instead of Apps Script?: Not directly. The built-in TEXT formula and date formats in Sheets always render in the spreadsheet's display timezone — there is no formula argument to specify a different target zone. If you need a cross-timezone display in a cell formula rather than a script, the only option is a custom Apps Script function called as =CONVERT_TZ(A2, 'UTC', 'America/New_York') using the same Utilities.formatDate approach.

// one good script a week