Guides6 min read

How to Convert Time Zones in Excel (With Formulas)

Excel doesn't know what timezone your data is in. That's not a bug — it's just how spreadsheets work. A cell showing 09:00 could be 9 AM New York, 9 AM London, or 9 AM Tokyo, and Excel treats all three identically.

The problem shows up fast. You're tracking meeting times across offices, logging timestamps from a database, or building a payroll sheet with remote workers — and suddenly the "right" time in one column is the wrong time everywhere else.

Here's exactly how to fix it.

The Core Formula: Simple UTC Offset Math

For most use cases, you don't need a plugin. You need one formula.

The logic: Every timezone is a fixed offset from UTC. New York (EST) is UTC-5. London (GMT) is UTC+0. India (IST) is UTC+5:30. To convert between any two timezones, you add or subtract the difference.

In Excel, time is stored as a decimal fraction of a day. 1 hour = 1/24. So to shift a time by 5.5 hours (the EST-to-IST difference), you add 5.5/24 to the cell.

Basic formula:

= A2 + (offset_hours / 24)

Real example: If A2 contains a time in New York (EST) and you want India Standard Time (IST), the difference is +10.5 hours:

= A2 + (10.5/24)

That's it. Format the result cell as a time, and you're done.

📊 India Standard Time (IST) is UTC+5:30 — one of only a handful of timezones worldwide using a 30-minute offset. This half-hour difference catches people off guard every time when they're doing the math manually. — IANA Time Zone Database

Building a Proper Timezone Converter in Excel

The single-formula approach breaks down when you're handling multiple timezones or when Daylight Saving Time is involved. Here's the setup that actually holds up:

Step 1 — Create a reference table

In a separate sheet (or a named range), build a lookup table:

| Timezone | Standard Offset (hours) | DST Offset (hours) | |---|---|---| | EST (New York) | -5 | -4 | | CST (Chicago) | -6 | -5 | | MST (Denver) | -7 | -6 | | PST (Los Angeles) | -8 | -7 | | GMT (London) | 0 | +1 | | IST (India) | +5.5 | +5.5 | | JST (Tokyo) | +9 | +9 | | AEST (Sydney) | +10 | +11 |

Notice India and Japan don't change for DST — they're fixed offsets year-round.

Step 2 — Add a date column

DST matters. A New York timestamp from July is in EDT (UTC-4). The same timestamp from January is in EST (UTC-5). Without a date, you can't know which offset applies.

Your spreadsheet needs a date column alongside your time column.

Step 3 — Use VLOOKUP + IF to pull the right offset

= VLOOKUP(B2, TimezoneTable, 2, FALSE)

Where B2 is the timezone name and TimezoneTable is your named range. This pulls the standard offset.

For DST-aware lookups, you'll need to add a condition: if the date falls in DST range (roughly March–November for US zones), use column 3 (DST offset) instead of column 2.

= IF(AND(MONTH(A2)>=3, MONTH(A2)<=11),
    VLOOKUP(B2, TimezoneTable, 3, FALSE),
    VLOOKUP(B2, TimezoneTable, 2, FALSE))

This is a simplification — DST start and end dates are specific Sundays, not calendar months. But for most business use cases, month-level precision is close enough.

Handling Unix Timestamps in Excel

If your data came from a database, you might be looking at Unix timestamps — numbers like 1748843400. These count seconds since January 1, 1970 UTC.

To convert a Unix timestamp in Excel:

= (A2 / 86400) + DATE(1970, 1, 1)

This gives you the UTC datetime. From there, apply your timezone offset formula as above.

One gotcha: some systems return milliseconds instead of seconds — if your timestamps are 13 digits instead of 10, divide by 86400000 instead of 86400.

📊 Over 4.8 million Americans work remotely full-time — US Census Bureau. For distributed teams logging work hours across timezones, proper timestamp handling in spreadsheets isn't optional — it's the difference between accurate payroll and a confused accountant.

The DST Problem — Why Your Formula Breaks Twice a Year

Here's where most Excel timezone solutions fail. You build the formula, it works great for six months, then suddenly your New York times are off by an hour.

DST is the culprit. Every year, on the second Sunday of March, EST becomes EDT — and the UTC offset changes from -5 to -4. A formula hardcoded to +10.5 for EST-to-IST is now off by an hour, giving you +9.5 when it should still be +10.5.

That said, India never observes DST. IST is UTC+5:30 year-round. So the EST-IST gap changes — not because India moved, but because the US did.

The clean solution: instead of hardcoding offsets, always calculate from UTC. Store all your source times in UTC, then convert to display timezone at the point of reading. It's more setup upfront, but you never chase a DST bug again.

Google Sheets vs Excel: Small Differences That Matter

If you're using Google Sheets instead of Excel, there's one useful function Excel doesn't have natively:

=GOOGLEFINANCE("CURRENCY:USDINR")  ← irrelevant

Actually, the relevant one is the TEXT function with timezone-aware formatting — but Sheets doesn't handle this natively either. Both tools have the same limitation: they don't know what timezone a time value represents.

The practical difference is collaboration. Sheets updates in real-time across users in different countries. If you're sharing a live schedule with a team in Bangalore and one in Berlin, Sheets is the better environment — but the timezone math is the same either way.

When Excel Isn't Enough

For one-off lookups, the formula approach works fine. But if you're regularly converting times between specific city pairs — New York to India, London to Sydney, EST to IST — a dedicated tool is faster than building and maintaining a spreadsheet.

The time zone converter handles any pair instantly, including DST transitions. And if you're looking at clock differences right now rather than converting a specific time, the world clock shows live times across multiple cities simultaneously — which is usually what you actually need when you're checking whether it's a reasonable hour to call someone.

Frequently Asked Questions

How do I convert EST to IST in Excel?

Add 10.5 hours to your EST time: = A2 + (10.5/24). During US Daylight Saving Time (roughly March–November), EST becomes EDT, and the gap narrows to 9.5 hours: = A2 + (9.5/24). India doesn't observe DST, so IST stays fixed at UTC+5:30 year-round.

Can Excel automatically detect Daylight Saving Time?

Not natively. Excel doesn't know what timezone a cell represents, so it can't automatically apply DST adjustments. You'll need to add a date-based IF condition to your formula, or build a lookup table with separate standard and DST offsets.

What's the formula to convert UTC to any timezone in Excel?

= UTC_cell + (offset_hours / 24). For UTC to IST (+5.5 hours): = A2 + (5.5/24). For UTC to PST (-8 hours): = A2 + (-8/24) or = A2 - (8/24).

How do I handle Unix timestamps in Excel?

Use = (A2 / 86400) + DATE(1970, 1, 1) to convert seconds since epoch to an Excel datetime in UTC. If your timestamps are in milliseconds (13 digits), divide by 86400000 instead.

Why are my converted times off by an hour?

Almost always a DST issue. If your source timezone observes DST and your formula uses a fixed offset, it'll be wrong during daylight saving months. Check whether the source timezone is currently in standard or daylight time, and adjust the offset accordingly.

EST to IST: How to Convert Eastern Time to India Standard Time — Full conversion table and best overlap hours, no spreadsheet required.
How to Calculate Time Difference Between Two Countries — UTC offset reference for 10 major cities.
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

← All articles