How to Convert Time Zones in Excel (With Formulas)
Excel doesn't know what time zone your data is in. A cell showing 09:00 could be 9 AM in New York, London or Tokyo, and Excel treats all three the same. That is not a bug. A spreadsheet stores a time as a plain number, and a number has no place attached to it.
So you do the conversion yourself, and the part that goes wrong is daylight saving time. This page gives formulas that handle it properly, and shows the answers they should produce so you can check your own sheet.
The Core Formula: Simple UTC Offset Math
Excel stores a date and time as one number: the whole part counts days, and the fraction is the time of day. One hour is 1/24, so shifting a time by 5.5 hours means adding 5.5/24.
Put a UTC date and time in A2 and use:
= A2 + (offset_hours / 24)
For India, which is UTC+5:30 all year:
= A2 + (5.5 / 24)
Format the result cell as a date and time, for example yyyy-mm-dd hh:mm. If the cell holds only a time of day, a shift that crosses midnight can produce a negative number, which Excel shows as ####. Keep the date with the time, or wrap the formula in MOD(..., 1) to keep only the time of day.
The offset has to be the one in force on that date. India stays UTC+5:30, but New York is UTC-5 in winter and UTC-4 in summer. That is why a single +10.5 between New York and India is right only part of the year.
Building a Proper Timezone Converter in Excel
The setup that holds up has three parts: store the source in UTC, look up each zone's standard and summer offsets, and work out whether summer time applies on that date.
Step 1: A reference table. Put this on a separate sheet and name the range TimezoneTable:
| Zone | Standard offset | Summer offset | Summer time rule |
|---|---|---|---|
| New York | -5 | -4 | US |
| Chicago | -6 | -5 | US |
| Denver | -7 | -6 | US |
| Los Angeles | -8 | -7 | US |
| London | 0 | 1 | EU |
| Berlin | 1 | 2 | EU |
| India | 5.5 | 5.5 | none |
| Tokyo | 9 | 9 | none |
Sydney isn't in the table on purpose. Its summer runs from October to April, so it needs a southern-hemisphere rule of its own, and the US and EU rules below would give it the wrong answer.
Step 2: The date matters. Without the date you can't tell whether New York is on UTC-5 or UTC-4, so keep the full date and time, not just the time of day.
Step 3: Work out when summer time starts and ends. The US moves on the second Sunday of March and the first Sunday of November. The EU and UK move on the last Sunday of March and the last Sunday of October. Months are not precise enough, because the switch falls in the middle of the month. In Excel, WEEKDAY returns 1 for Sunday, so these formulas find the Sundays. With the UTC time in A2, these helper cells, placed off to the side of your data, find the Sundays for the US (Eastern):
B2 = DATE(YEAR(A2),3,8)
C2 = DATE(YEAR(A2),11,1)
F2 = B2 + MOD(8-WEEKDAY(B2),7) + 7/24
G2 = C2 + MOD(8-WEEKDAY(C2),7) + 6/24
F2 is when summer time starts and G2 when it ends. The 7/24 and 6/24 are the moments of the switch in UTC: 2:00 AM Eastern standard time is 07:00 UTC in March, and 2:00 AM Eastern daylight time is 06:00 UTC in November. For Central time add one hour to each (8/24 and 7/24), for Mountain two, for Pacific three.
Then the New York time is:
= A2 + IF(AND(A2>=F2, A2<G2),
-4, -5) / 24
For the EU and UK the switch is the last Sunday of March and October, at 01:00 UTC in every country:
H2 = DATE(YEAR(A2),3,31)
I2 = DATE(YEAR(A2),10,31)
J2 = H2 - MOD(WEEKDAY(H2)-1,7) + 1/24
K2 = I2 - MOD(WEEKDAY(I2)-1,7) + 1/24
Use IF(AND(A2>=J2, A2<K2), 1, 0) for London and IF(..., 2, 1) for Berlin. To look the offsets up from the table instead of typing them, VLOOKUP(L2, TimezoneTable, 3, FALSE) returns the summer offset and column 2 the standard one (here L2 holds the zone name); use the first inside the IF and the second outside it.
I tested these formulas against the time zone database on 357 UTC moments around the 2026, 2027 and 2028 clock changes, including the minutes either side of each switch, and they gave the right local time every time.
A worked example: a 10:00 AM meeting in Los Angeles converted to Tokyo, which is UTC+9 all year.
| Los Angeles time | Date | UTC | Tokyo time |
|---|---|---|---|
| 10:00 AM PST | January 15, 2026 | 18:00 | 3:00 AM on January 16 |
| 10:00 AM PDT | July 15, 2026 | 17:00 | 2:00 AM on July 16 |
Two things show up here. The Tokyo time lands on the next calendar day, which is why the cell must keep the date. And it moves by an hour between January and July although Tokyo's clocks don't, because Los Angeles changed. The time zone converter gives the same two answers if you want to check your sheet.
Handling Unix Timestamps in Excel
If your data comes from a database or an API, you may have Unix timestamps: numbers like 1748843400, the seconds since January 1, 1970 UTC. This converts one to an Excel date and time in UTC:
= (A2 / 86400) + DATE(1970, 1, 1)
1748843400 becomes 2025-06-02 05:50 UTC. From there, apply an offset as above.
Some systems return milliseconds instead of seconds. A 13-digit number is in milliseconds, so divide by 86400000 instead of 86400. The epoch time converter can check a single value if you aren't sure which one you have, and what is epoch time explains why these numbers are always UTC.
The DST Problem: Why Your Formula Breaks Twice a Year
The classic failure is a formula with a fixed offset. It works for months, then every New York time is an hour off. New York moved its clock on the second Sunday of March; the formula didn't.
India never changes its clocks, so New York to India is 10.5 hours in winter and 9.5 in summer. A cell hardcoded to +10.5 is right until March and wrong from then until November.
A month check, such as "between March and November", is the usual shortcut. It treats every day of March and November as summer, so it is wrong for March 1 to 7 and for November 1 to 30 (the US changed on March 8 and November 1 in 2026), and anything on those days comes out an hour out. The Sunday formulas above avoid this.
The reliable habit is the one in step 1: store source times in UTC, and convert to a local time only for display. A UTC time never has a daylight saving problem, because UTC doesn't change.
Google Sheets vs Excel: Small Differences That Matter
The formulas above use DATE, WEEKDAY, MOD, IF and AND, which work the same way in Google Sheets. Neither tool has a built-in function that converts a time between named zones, so the offset work stays yours in both.
The practical difference is collaboration. Sheets updates in real time for people in different countries, so a shared schedule between a team in Bangalore and one in Berlin is easier there.
When Excel Isn't Enough
A spreadsheet is the right tool when you have a column of times to convert. For a single conversion, or a pair you check often, it is slower than a converter. The time zone converter handles any pair for a chosen date, reading the zone rules from your browser, so keep the browser up to date. If you want the current time in several cities at once, the world clock shows them live; it doesn't remember your cities after a reload.
If you stay in Excel, the checklist is short: keep the date with the time, store UTC, use the Sunday formulas rather than months, and test your sheet with the two Los Angeles to Tokyo rows in the table above before you trust it.
Related Articles
- EST to IST Converter: every hour converted for both seasons, no spreadsheet needed.
- How to Calculate Time Difference Between Two Countries: the method behind the offsets, and why the gap moves by an hour with the season.
- What Is Epoch Time? Unix Timestamps Explained Simply: how Unix timestamps work and why they're always in UTC.
Related tools: Time Zone Converter · Epoch Time Converter · World Clock