Date & Time
Convert a fixed-format UTC timestamp with an explicit hour offset
Parse YYYY-MM-DDTHH:MM:SSZ timestamps and apply a supplied fixed UTC offset. Named-zone and daylight-saving rules are not inferred.
Why DATE + TIME slicing beats DATEVALUE on ISO-8601 API logs
This example accepts the exact 20-character shape YYYY-MM-DDTHH:MM:SSZ with whole seconds. It is not a general ISO-8601 parser: fractional seconds, date-only strings, signed offsets and leap-second notation require a different parser.
DATE and TIME build a serial value from the numeric components. Adding B2/24 applies a fixed offset and can cross midnight. Supply the correct offset for that instant yourself; the formula has no time-zone database or daylight-saving logic. The displayed result is text, not a zone-aware timestamp.
| Raw ISO-8601 UTC String (A) | UTC Offset Hours (B) | Local Datetime Result |
|---|---|---|
| "2026-10-30T14:45:30Z" | -4 (New York EDT) | "2026-10-30 10:45:30" |
| "2026-11-01T02:15:00Z" | -5 (fixed offset example, not New York at this instant) | "2026-10-31 21:15:00" (Previous day!) |
| "2026-10-30T22:30:00Z" | +9 (Tokyo JST) | "2026-10-31 07:30:00" (Next day!) |
Parse ISO-8601 UTC string and shift to local timezone
A2 holds the ISO-8601 UTC string and B2 holds the signed UTC offset in hours (e.g. -4, +2, +5.5 for IST).
=IFERROR(IF(OR(LEN(A2)<>20,MID(A2,5,1)<>"-",MID(A2,8,1)<>"-",MID(A2,11,1)<>"T",MID(A2,14,1)<>":",MID(A2,17,1)<>":",RIGHT(A2,1)<>"Z",NOT(ISNUMBER(B2)),ABS(B2)>14),"Invalid timestamp or offset",IF(OR(TEXT(DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(MID(A2,9,2))),"yyyy-mm-dd")<>LEFT(A2,10),VALUE(MID(A2,12,2))<0,VALUE(MID(A2,12,2))>23,VALUE(MID(A2,15,2))<0,VALUE(MID(A2,15,2))>59,VALUE(MID(A2,18,2))<0,VALUE(MID(A2,18,2))>59),"Invalid timestamp or offset",LET(s, A2, utc, DATE(VALUE(LEFT(s, 4)), VALUE(MID(s, 6, 2)), VALUE(MID(s, 9, 2))) + TIME(VALUE(MID(s, 12, 2)), VALUE(MID(s, 15, 2)), VALUE(MID(s, 18, 2))), loc, utc + B2 / 24, TEXT(INT(loc), "yyyy-mm-dd") & " " & RIGHT("00" & HOUR(loc), 2) & ":" & RIGHT("00" & MINUTE(loc), 2) & ":" & RIGHT("00" & SECOND(loc), 2)))),"Invalid timestamp or offset")Extract just the local calendar date for daily revenue grouping
The date belongs to the supplied fixed offset. Whether it is the appropriate reporting day depends on your actual timezone and business rules.
=IFERROR(IF(OR(LEN(A2)<>20,MID(A2,5,1)<>"-",MID(A2,8,1)<>"-",MID(A2,11,1)<>"T",MID(A2,14,1)<>":",MID(A2,17,1)<>":",RIGHT(A2,1)<>"Z",NOT(ISNUMBER(B2)),ABS(B2)>14),"Invalid timestamp or offset",IF(OR(TEXT(DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(MID(A2,9,2))),"yyyy-mm-dd")<>LEFT(A2,10),VALUE(MID(A2,12,2))<0,VALUE(MID(A2,12,2))>23,VALUE(MID(A2,15,2))<0,VALUE(MID(A2,15,2))>59,VALUE(MID(A2,18,2))<0,VALUE(MID(A2,18,2))>59),"Invalid timestamp or offset",LET(s, A2, utc, DATE(VALUE(LEFT(s, 4)), VALUE(MID(s, 6, 2)), VALUE(MID(s, 9, 2))) + TIME(VALUE(MID(s, 12, 2)), VALUE(MID(s, 15, 2)), VALUE(MID(s, 18, 2))), TEXT(INT(utc + B2 / 24), "yyyy-mm-dd")))),"Invalid timestamp or offset")Try the example
Use the original values or change them to test your understanding.