How to convert time zones in Excel

Add the offset divided by 24

The sheet is a support log. Every call is stamped in UTC in column B, and each office wants to see its own local time. Excel stores a date and time as a number of days, so one hour is 1/24. An offset of -5 hours is -5/24 of a day.

Put the offsets in a table instead of typing them into each formula: office names in F2:F6 and offsets in decimal hours in G2:G6. Then one formula fills down the whole log, and when an office's offset changes you edit one cell. Format the result as a date and time (for example yyyy-mm-dd hh:mm with Ctrl+1 > Custom) so you can see the date as well as the clock time.

To go the other way, from local time to UTC, subtract the offset instead: =B2-XLOOKUP(C2,F2:F6,G2:G6)/24. XLOOKUP needs Excel 365 or 2021; in older versions use VLOOKUP(C2,$F$2:$G$6,2,FALSE) in the same place.

Let the date roll, or keep only the time

When column B holds a full date and time, the date moves by itself. Call C-205 was logged at 1:15 UTC on 3 November. Five hours earlier is still 2 November in New York, and the formula shows that. This is why UTC with a full date is the safest thing to store: every conversion is correct, including the day.

A time on its own is different. J2 holds just 1:15 with no date. Subtracting five hours gives -0.15625, a negative time, and Excel shows ##### in a time-formatted cell because it cannot display negative times. Wrap time-only results in MOD(...,1), which wraps them back into a single day: =MOD(J2-5/24,1) gives 20:15. The catch is that MOD throws the date away, so you no longer know it was the previous day. If the day matters, add the date to the time before converting.

Half-hour and 45-minute zones

Not every offset is whole hours. India is UTC+5:30 and Nepal is UTC+5:45. In decimal hours those are 5.5 and 5.75, and /24 works the same way: call C-207 at 4:10 UTC is 9:55 in Kathmandu.

The trap is mixing two kinds of offset. If someone types 5:45 into the offset cell, Excel stores it as a time (5 hours 45 minutes as a fraction of a day), which is already in days. Add it directly, or with =B8+TIME(5,45,0); dividing it by 24 again shrinks it to about 14 minutes. The opposite mistake is adding the decimal 5.75 without /24: Excel adds 5.75 days, and the call jumps to 2026-11-08 22:10. Pick decimal hours for the whole table, since negative offsets such as -5 cannot be typed as a time anyway.

Offsets often arrive as text labels like UTC+05:30, as in H2:H6. Read the sign from the 4th character and the hours and minutes from the digits. The formula below turns UTC-05:00 into -5; on UTC+05:45 it gives 5.75.

Daylight saving: a dated offset table

One fixed offset per office is right only between clock changes. Call C-201 was on 30 October, when New York was still on daylight time (UTC-4). The simple table above uses -5 and shows 9:00, an hour early. Excel knows nothing about daylight saving, so you have to tell it when each offset starts.

Make a second table with one row per change: office in L, the UTC moment the offset starts in M, and the offset in N. Look up the office's row whose start is on or before the call time. Fill the dates from an official source for each year you need; they move every year and differ by country, and some places have no daylight saving at all. Keep each office's rows in date order, oldest first.

XLOOKUP looks for the 1 that marks "this office and already started", and the final -1 searches from the bottom up, so it lands on the latest change that applies. For C-201 that is -4, giving 10:00. For C-202 on 2 November it returns -5. In Excel 2019 and older, =LOOKUP(2,1/((L2:L5=C2)*(M2:M5<=B2)),N2:N5) does the same job.

Convert between two zones (meeting planner)

To go from one local zone to another, add the difference between the two offsets. A meeting at 9:00 New York time on 5 November is 14:00 UTC (9:00 minus -5 hours). In Sydney, 16 hours ahead of New York at that date, it is 1:00 the next morning; in Kathmandu it is 19:45 the same evening.

For anything you will reuse, convert to UTC first and then to each zone, rather than storing pairwise differences. Two lookups against one table is easier to check than a grid of differences that each need updating when one place changes its clocks.

Which approach to use

Store UTC with the full date as the source of truth and add a local-time column per office: the stamps sort correctly, durations between them are right, and nothing breaks when the clocks change. Use the fixed offset table for offices with no daylight saving or for a short period; switch to the dated table once your data spans a clock change. Use MOD only for time-only values where the day does not matter.

Watch where the times come from. NOW() returns your computer's local clock time, not UTC, so stamping a log with it in two offices gives two different time bases. In Power Query, datetimezone values carry an offset, and DateTimeZone.SwitchZone moves them to another fixed offset, but it still does not know the daylight-saving dates for you.