How to Convert Time Zones in Microsoft Excel

The core idea

Excel stores times as fractions of a day. Adding TIME(3,0,0) to a datetime cell shifts it forward by exactly 3 hours. Converting between time zones is just adding or subtracting the offset between them.

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 does not have a built-in IANA time-zone database, so it cannot know that New York is UTC−5 in winter and UTC−4 in summer. If your data spans DST transitions, a single fixed offset will silently produce times that are off by an hour for part of the year.

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.

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, converting 2026-03-08 02:30 from New York to London requires knowing that the US spring-forward happens that morning, shifting the offset from −5 to −4. Clockjumper accounts for this automatically, while a fixed Excel formula would be an hour off.

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