Convert timestamps across time zones: UTC offsets and midnight rollover

Full timestamps vs clock-only times across midnight

Because 1 hour equals 1/24 of a spreadsheet day, shifting a full UTC timestamp (date + time) to a local offset requires only UtcTimestamp + (OffsetHours / 24). Adding +3 hours to 2026-05-10 22:30 UTC automatically advances the date to 2026-05-11 01:30.

When a cell stores only a clock time without a date (e.g., 02:15), subtracting 5 hours produces a negative fraction (-2.75/24, approximately -0.1145833), which displays #### in Excel unless wrapped in MOD(Time + Offset/24, 1).

Supply the correct UTC offset for that specific timestamp. This arithmetic does not look up named time zones or daylight-saving transitions. MOD discards the date change deliberately and is appropriate only when a clock-only result is wanted.

Shift full date-time timestamps cleanly

Extracting the shifted date and zero-padded hour/minute confirms both forward (+3h -> May 11, 01:30) and backward (-5h -> May 9, 21:15) date rollovers.

=TEXT(INT(A2 + B2 / 24), "yyyy-mm-dd") & " " & RIGHT("00" & HOUR(A2 + B2 / 24), 2) & ":" & RIGHT("00" & MINUTE(A2 + B2 / 24), 2)

Wrap time-only values with MOD(..., 1)

MOD(TIME(2, 15, 0) - 5 / 24, 1) * 24 wraps -2.75 hours cleanly to 21.25 decimal hours (21:15).

=ROUND(MOD(TIME(2, 15, 0) - 5 / 24, 1) * 24, 2)

Further reading

• Microsoft Support — MOD function