Search code examples
dategoogle-sheetstimetimezoneutc

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:

    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

    spreadsheet demo copy

    for other conversions, follow:

    #whatatimetobealivehashtaghashtag