How to Convert Time Zones in Excel and Google Sheets

If you manage schedules, reports, or data across time zones in a spreadsheet, you need to convert times between zones manually. Excel and Google Sheets do not have built-in timezone conversion functions, but the math is straightforward.

The core principle is simple: add or subtract the hour difference between the two zones. If a timestamp is in EST (UTC-5) and you need IST (UTC+5:30), the difference is 10 hours and 30 minutes. Add that to the EST time to get IST.

The complication is daylight saving time. If either zone observes DST, the offset changes depending on the date. A formula that works in January may produce the wrong result in July.

If you know the hour difference between two zones and neither zone changes for DST, the formula is simple. Assume cell A1 contains a time in the source zone:

In Google Sheets: =A1 + (offset_hours / 24). For example, to convert EST to IST (10.5 hours ahead): =A1 + (10.5 / 24). To convert PST to GMT (8 hours ahead): =A1 + (8 / 24).

In Excel, the formula is the same: =A1 + (10.5 / 24). Time in Excel is stored as a fraction of a day, so adding 10.5/24 adds 10 hours and 30 minutes.

To convert in the opposite direction (subtract hours), use a negative offset: =A1 - (10.5 / 24). If the result is negative, the time crosses midnight. You can handle this with: =MOD(A1 - (10.5 / 24), 1).

DST makes timezone formulas more complex because the offset changes depending on the date. In the US, DST runs from the second Sunday of March to the first Sunday of November. During DST, Eastern Time is UTC-4 instead of UTC-5.

To handle DST in a spreadsheet, you need to check the date and apply the correct offset. Here is a Google Sheets formula that converts EST to IST with DST awareness:

Assuming A1 is a date-time value in Eastern Time: =A1 + IF(AND(MONTH(A1) > 3, MONTH(A1) < 11), 9.5, 10.5) / 24. This checks if the month is between April and October (DST period) and uses a 9.5-hour offset instead of 10.5.

This is a simplified check. For exact DST dates (which vary by year), you need a more precise formula that checks the specific Sunday when clocks change. For most practical purposes, the month-based approximation works.

Here are the most common conversions and their offsets:

EST to IST (India): Add 10.5 hours. EST to GMT: Add 5 hours. EST to JST (Japan): Add 14 hours. PST to EST: Add 3 hours. PST to GMT: Add 8 hours. PST to IST: Add 13.5 hours.

During US daylight saving time (EDT), subtract 1 hour from the offset. EDT to IST is 9.5 hours. EDT to GMT is 4 hours.

For a quick reference, use the time zone converter to check the exact offset for any specific date.

Google Sheets has a built-in function: =NOW() which returns the current date-time in your local timezone. To get UTC time, you can use the formula: =NOW() + (OFFSET / 24) where OFFSET is your local offset from UTC.

To format a cell as time, select the cell and choose Format, then Number, then Time. To format as a specific timezone label, concatenate the time with a text string like: =TEXT(A1, "HH:MM AM/PM") and add the timezone label.

For bulk conversions, apply the formula to an entire column. If column A has timestamps in EST and you need IST in column B, put =A1 + (10.5 / 24) in B1 and drag down.

Excel stores dates as serial numbers and times as fractions of a day. This means time arithmetic works the same as in Google Sheets: add or subtract fractions of 24.

To convert a time in cell A1 from EST to IST in Excel: =A1 + 10.5/24. To handle date crossing: =MOD(A1 + 10.5/24, 1).

For timezone-aware spreadsheets, consider adding a helper column that labels each timestamp with its timezone. This prevents confusion when someone opens the spreadsheet later and does not know which zone the times are in.

Excel does not have a native timezone conversion function. For complex timezone logic, you may need to use VBA macros or switch to Google Sheets, which has better timezone support through Apps Script.

• Use city names instead of ambiguous timezone abbreviations.
• Repeat the selected time on booking confirmation pages.
• Check daylight saving changes before publishing event times.

Converting time zones in spreadsheets is simple math: add or subtract the hour difference. The complication is DST, which changes the offset depending on the date. For most use cases, a month-based DST check is sufficient.

For quick one-off conversions, use the time zone converter instead of a spreadsheet. For recurring data that needs timezone conversion, build the formula into your sheet and test it across DST transition dates.