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: A location's UTC offset must be evaluated for the specific date and time. Fixed-offset zones such as India stay constant, while regions such as New York and London change offset when daylight saving time begins or ends. For example, in winter (standard time) New York is UTC-5 (EST), London is UTC+0 (GMT), and India is UTC+5:30 (IST). Once you know the offsets that apply on that date, 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 UTC-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. The March–November check only fits US zones: the UK and EU switch on different Sundays, and Sydney's daylight saving runs October to April, so those rows need their own date rule rather than this one.
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, Eastern Time switches from EST to EDT, and the UTC offset changes from -5 to -4. New York-to-India (IST) is a real 10.5-hour gap in winter (EST) and 9.5 hours in summer (EDT). A formula hardcoded to +10.5 keeps returning +10.5 after that switch, when the correct gap is now +9.5 — it's off by an hour because it never updates, not because the real gap somehow stayed fixed.
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), Eastern Time switches to 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.
Related Articles
- 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