How to Convert Time Zones in Google Sheets

The core idea

Every Sheets datetime is a serial number under the hood — the day count in the integer part, the fraction of the day in the decimal.TIME(3,0,0) is literally the value 0.125, so adding it walks a cell three hours forward. That is the entire mechanism behind a manual timezone conversion in Sheets: add to move east, subtract to move west.

Basic formula

To convert a time in cell A2 from one zone to another with an offset of +3 hours:

=A2 + TIME(3, 0, 0)

For a negative offset (e.g. PST → EST is +3 hours, but EST → PST is −3):

=A2 - TIME(3, 0, 0)

Keep the result inside one day

If you only care about the wall-clock time (not the date), MOD wraps the result back into a 24-hour window:

=MOD(A2 + TIME(3, 0, 0), 1)

Format the cell as Format → Number → Time so it renders as 14:30 instead of a decimal.

Convert and format in one step

Wrap the result in TEXT for a clean display string:

=TEXT(MOD(A2 + TIME(3, 0, 0), 1), "hh:mm AM/PM")

Make the offset configurable

Put the offset in its own cell (say B1) so you can change it without rewriting every formula:

=A2 + TIME($B$1, 0, 0)

Set the spreadsheet's own time zone first

Before you write a single offset, open File → Settings → General and pin the spreadsheet's time zone to a specific IANA zone (for example America/New_York). This value controls what NOW(), TODAY(), and any Apps Script triggers evaluate as — the browser's local time is not used. Without pinning it, two collaborators in different countries will see different "now" values in the same file and your conversions will drift by the difference between their laptops.

The Daylight Saving Time problem

Sheets exposes no IANA database inside a formula. Even though you pinned the spreadsheet zone in Settings, cell math still operates on raw serial numbers with no notion of transitions. That means a single fixed offset silently produces wrong results on either side of a DST boundary — March, April, October, and November data will be an hour off unless you either segment the offset per date range or move the conversion into Apps Script.

Two practical workarounds:

• Maintain a small lookup table mapping date ranges to offsets and use VLOOKUP to pick the right one per row.
• Use a tool that already knows IANA time zones — like Clockjumper, which handles DST automatically for every city.

The Apps Script escape hatch

When formulas are not enough, Apps Script gives you the realUtilities.formatDate API, which accepts an IANA zone string and applies the correct DST offset for the given date. Add a custom function to Extensions → Apps Script and call it from any cell:

/**
 * =TZCONVERT(A2, "America/New_York", "Europe/London")
 * Converts a datetime from one IANA zone to another, DST-aware.
 */
function TZCONVERT(dt, fromZone, toZone) {
  if (!(dt instanceof Date)) dt = new Date(dt);
  var iso = Utilities.formatDate(dt, fromZone, "yyyy-MM-dd'T'HH:mm:ss");
  var asFromZone = new Date(iso + Utilities.formatDate(dt, fromZone, "XXX"));
  return Utilities.formatDate(asFromZone, toZone, "yyyy-MM-dd HH:mm");
}

Unlike a formula-only workbook, this reads the current IANA data shipped with Google's Java runtime, so DST rules stay correct as governments amend them (Chile, Iran, Lebanon have all shifted rules within the last few years).

Common offsets cheat sheet

PST→EST, CST→EST and UTC→JST hold year-round because both zones shift together, or neither does. EST→IST does not: from mid-March to early November, when US clocks move to daylight time and India's do not, the offset is +9:30.