How to Convert Time Zones in Excel: 3 Reliable Methods
Three reliable ways to convert time zones in Microsoft Excel, including a DST-aware approach with the TIMEZONE / NOW patterns and a faster shareable-link alternative.
Excel handles time as a fraction of a day, which makes time-zone math simple in principle and annoying in practice once daylight saving time enters the picture. Here are three approaches, from quickest to most robust.
1. Add or subtract the offset directly
If A2 is a UTC timestamp and you want London during BST (UTC+1):
=A2 + 1/24
Fast and obvious. Wrong half the year, because London is UTC+0 in winter (GMT).
2. Use a lookup table for DST
Maintain a small table of start date, end date, offset per zone, then XLOOKUP against it:
=A2 + XLOOKUP(A2, dst_starts, dst_offsets, 0, -1) / 24
This works year-round, but you have to update the table when DST rules change — which they have, repeatedly, in jurisdictions like Morocco, Brazil and parts of the US.
3. Use Power Query (recommended for serious work)
Power Query exposes DateTimeZone.SwitchZone and DateTimeZone.ToLocal, which use the OS time zone database. Load your column as a DateTimeZone type, then:
= DateTimeZone.SwitchZone([Timestamp], -5, 0)
Power Query handles offsets correctly, but it still doesn't know named IANA zones — you pass numeric offsets and own the DST decision.
When a link beats a cell
If the converted time is going into an email, a calendar invite or chat, paste a shareable converter link instead. Use our time zone converter — it's DST-aware and renders in the reader's own local time.
Frequently asked
- Does Excel know IANA time zones?
- Not from worksheet formulas. The DateTimeZone type in Power Query handles numeric offsets, but named zones like Europe/London are not first-class. You'll need a lookup or an external tool for DST-correct named zones.
- Why does my converted time look like a number?
- Excel stored the result as a serial number. Right-click the cell, choose Format Cells, and apply a date or time format like yyyy-mm-dd hh:mm.
- How do I convert an entire column?
- Write the formula once in the first row and double-click the fill handle to copy it down, or use a structured-reference formula inside an Excel table so it auto-fills new rows.