convert UTC timezone into custom local time with daylight savings offset (and back) in google sheets

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

Solution

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: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))))))

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, "yyyymmdd\Thhmmss\Z")))

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), "yyyymmdd\Thhmmss\Z"))))))

spreadsheet demo copy

for other conversions, follow:

#whatatimetobealivehashtaghashtag