I’m looking for a way to convert standard UTC timestamps into a valid Date Time values of local timezone with daylight saving oscillation in mind.
…using this unnamed calendar app, that does not have option to set up custom timezone, so all events are recorded in UTC format, but offset by a local timezone + daylight saving.
for example: birthday party is 20/02/2023 00:00:00 but UTC timestamp reads 20230219T220000Z so the task is to transform it into “real” readable format like:
| from UTC | to Date time |
|---|---|
| 20230219T220000Z | 20/02/2023 00:00:00 |
| 20230519T210000Z | 20/05/2023 00:00:00 |
| 20240901T210000Z | 02/09/2024 00:00:00 |
| 20230313T070000Z | 13/03/2023 09:00:00 |
| 20230403T050000Z | 03/04/2023 08:00:00 |
| 20230815T113000Z | 15/08/2023 14:30:00 |
quick search yielded few questions (like this one), but none of the answers tackled the daylight savings aspect while conversion from UTC to dd/mm/yyyy took place, within the scope of formulae execution
the daylight savings practices are tricky, and can depend on various factors (official, regional, local, custom…), and even standard DST can differ in multiple variations when and how transition takes place (in some countries it’s 2nd Sunday of month 11, in others it can be 1st or 4th or last – when month has only 3 or 5 days) as there are more than 100 unique versions on this planet and with combination of 38 timezones there can be countless mistakes lost in transition. therefore hard-coding (one variant) it into formula makes no sense. better solution includes a side table that will carry the logic of transition, for example:
where fields contain the following data validation rules:
- starting/ending month:
1,2,3,4,5,6,7,8,9,10,11,12 - starting/ending day:
1st,2nd,3rd,4th,last - starting/ending day of week:
mon,tue,wed,thu,fri,sat,sun - starting/ending time: valid time, either in
hh:mm:ssformat or in numeric time value format. fx:
=(F6<=0.999999994)*(F6>=0) - global time offset: valid time positive/negative duration, either in
-hh:mm:ssformat or in numeric time value format. fx:
=(F11<=0.999999994)*(F11>=-0.999999994)
and with this, we can find Date time value for Daylight savings transition of any (reasonable) year using the following arrayformula:
=INDEX(IFERROR(BYROW(A3:A30, LAMBDA(i, LET(p, "select max(Col1) group by Col1 pivot Col2",
d, YEAR(i)&"-"&F3, e, EOMONTH(d, ), s, SEQUENCE(e-d+1, 1, d*1), t, TEXT(s, "ddd"),
IF(REGEXMATCH(F4, "^[2-4]"), VLOOKUP(F5, SPLIT(FLATTEN(QUERY(QUERY({s, t}, p),,49)), " "),
LEFT(F4, 1)+1, ), XLOOKUP(F5, t, s,,,IF(F4="last", -1, 1)))+N(F11)+N(F6))))))
where XLOOKUP handles 1st and last state, and rest is handled by VLOOKUPing pivot table
converting UTC to datetime value [simple]
=INDEX(IF(A3:A30="",,REGEXREPLACE(A3:A30,
"(....)(..)(..)(.)(..)(..)(..)(.)", "$1-$2-$3 $5:$6:$7")*1))
converting datetime value to UTC [simple]
=INDEX(IF(B3:B30="",,TEXT(B3:B30, "yyyymmddThhmmssZ")))
converting UTC to datetime value with proper DST offset (and back)
for this, we combine everything from above and add two more fields
- local winter to UTC: offset between UTC and local winter time in duration
- local summer to UTC: offset between UTC and local summer time in duration
and use this formula:
=INDEX(IFERROR(BYROW(A3:A27, LAMBDA(i, LET(p, "select max(Col1) group by Col1 pivot Col2",
d, LEFT(i, 4)&"-"&F3, e, EOMONTH(d, ), s, SEQUENCE(e-d+1, 1, d*1), t, TEXT(s, "ddd"),
f, IF(REGEXMATCH(F4, "^[2-4]"), VLOOKUP(F5, SPLIT(FLATTEN(QUERY(QUERY({s, t}, p),,49)), " "),
LEFT(F4, 1)+1, ), XLOOKUP(F5, t, s,,,IF(F4="last", -1, 1)))+N(F11)+N(F6),
д, LEFT(i, 4)&"-"&F7, е, EOMONTH(д, ), с, SEQUENCE(е-д+1, 1, д*1), т, TEXT(с, "ddd"),
ф, IF(REGEXMATCH(F8, "^[2-4]"), VLOOKUP(F9, SPLIT(FLATTEN(QUERY(QUERY({с, т}, p),,49)), " "),
LEFT(F8, 1)+1, ), XLOOKUP(F9, т, с,,,IF(F8="last", -1, 1)))+N(F11)+N(F10),
x, REGEXREPLACE(i, "(....)(..)(..)(.)(..)(..)(..)(.)", "$1-$2-$3 $5:$6:$7")*1,
IF((x>f)*(x<ф), x+F13, x+F12))))))
and back:
=INDEX(IFERROR(BYROW(B3:B27, LAMBDA(i, LET(p, "select max(Col1) group by Col1 pivot Col2",
d, YEAR(i)&"-"&F3, e, EOMONTH(d, ), s, SEQUENCE(e-d+1, 1, d*1), t, TEXT(s, "ddd"),
f, IF(REGEXMATCH(F4, "^[2-4]"), VLOOKUP(F5, SPLIT(FLATTEN(QUERY(QUERY({s, t}, p),,49)), " "),
LEFT(F4, 1)+1, ), XLOOKUP(F5, t, s,,,IF(F4="last", -1, 1)))+N(F11)+N(F6),
д, YEAR(i)&"-"&F7, е, EOMONTH(д, ), с, SEQUENCE(е-д+1, 1, д*1), т, TEXT(с, "ddd"),
ф, IF(REGEXMATCH(F8, "^[2-4]"), VLOOKUP(F9, SPLIT(FLATTEN(QUERY(QUERY({с, т}, p),,49)), " "),
LEFT(F8, 1)+1, ), XLOOKUP(F9, т, с,,,IF(F8="last", -1, 1)))+N(F11)+N(F10),
TEXT(IF((i>f)*(i<ф), i-F13, i-F12), "yyyymmddThhmmssZ"))))))
spreadsheet demo copy
for other conversions, follow:
- gregorian to persian calendar
- persian to gregorian calendar
- gregorian to chinese lunar calendar
- constructing calendar
- number to indian currency
- convert ISO date to date time
- convert UNIX / epoch time to date time with milliseconds
- convert date string into date time (alt 1, alt 2)
- convert broken date into valid date value
- convert sum of times into duration
- convert text string
1d 2h 3m 4sinto time duration
#whatatimetobealivehashtaghashtag