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:
![enter image description here]()
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:ss format or in numeric time value format. fx:
=(F6<=0.999999994)*(F6>=0)
- global time offset: valid time positive/negative duration, either in
-hh:mm:ss format 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))))))
![enter image description here]()
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))
![enter image description here]()
converting datetime value to UTC [simple]
=INDEX(IF(B3:B30="",,TEXT(B3:B30, "yyyymmdd\Thhmmss\Z")))
![enter image description here]()
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))))))
![enter image description here]()
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), "yyyymmdd\Thhmmss\Z"))))))
![enter image description here]()
for other conversions, follow:
#whatatimetobealivehashtaghashtag
