How to Convert Time Zones in Microsoft Excel

The core idea

Under the hood, Excel represents every datetime as a serial number where the integer part is the day and the fractional part is the fraction of the day elapsed. That is why TIME(3,0,0) — which evaluates to 0.125 — advances any datetime by exactly three hours when you add it. A time-zone conversion is nothing more than that: add the offset to travel east, subtract it to travel 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 Cells → 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)

The Daylight Saving Time problem

Excel ships with no IANA tz database — the workbook has no idea that Chicago flips from UTC−6 to UTC−5 on the second Sunday of March, or that Sydney flips the opposite way in April. Hard-code a single offset and any row whose date crosses a transition boundary will be silently off by an hour, and the error is invisible unless you happen to spot-check the exact date.

Two practical workarounds:

• Maintain a small lookup table mapping date ranges to offsets and use VLOOKUP or XLOOKUP 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.

Power Query is the DST-aware escape hatch

If you need real DST handling inside Excel itself, Power Query (Get & Transform) is the closest built-in option. ItsDateTimeZone type carries an explicit offset, andDateTimeZone.SwitchZone shifts a value to a different offset. Power Query does not read the IANA database either — you still supply the target offset — but because you resolve the offset per row (via a helper column orDateTimeZone.ToLocalon the machine's clock), you can drive it from a lookup table without touching every formula:

= Table.AddColumn(Source, "London",
    each DateTimeZone.SwitchZone(
      DateTime.AddZone([UtcTime], 0),
      if [UtcTime] >= #datetime(2026,3,29,1,0,0)
         and [UtcTime] <  #datetime(2026,10,25,1,0,0)
      then 1 else 0))

Refresh runs the transform against the current data, so the offset logic lives in one place instead of being copy-pasted across thousands of cells.

Watch the 1900 vs 1904 date system

Excel for Windows defaults to the 1900 date system (day 1 = 1900-01-01); Excel for Mac historically defaulted to the 1904 date system (day 1 = 1904-01-01). When a workbook authored on one platform is opened on the other, or when you paste serials between workbooks with different settings, every datetime shifts by exactly 1,462 days (four years plus one leap day). If your converted times are off by four years, the two workbooks are on different date systems — fix it in File → Options → Advanced → When calculating this workbook before you diff the offsets.

How Clockjumper handles DST transitions Excel formulas miss

Excel formulas rely on static offsets. When a city switches to or from daylight saving time, the correct offset changes — sometimes mid-spreadsheet if your data spans months. Clockjumper uses an up-to-date IANA time-zone database and real transition rules to give you the exact local time for any past, present, or future date.

For example, a New York timestamp of 2026-03-08 09:00 lands on the morning of US spring-forward: earlier that same morning the offset was −5, but by 09:00 the clocks have already jumped to −4, so the correct London value is 13:00 UTC, not 14:00. A fixed Excel offset would be an hour off for every row on that date; Clockjumper resolves the offset per-timestamp.

When accuracy matters — for flight bookings, global meetings, or compliance reporting — pair your Excel workbooks with Clockjumper for instant, DST-aware conversions.

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.