Search code examples
datedatetimegoogle-sheetstimezoneiso8601

What datetime format is this 2022-04-05T12:39:34.579775Z and how to convert to US date time format?

What datetime format is this 2022-04-05T12:39:34.579775Z and how to convert to US date time format but in GMT timezone with a formula in Google Sheets when it appears in A1 and I want to return correct format in b1?

Solution

  • enter image description here

    try:

    =SUM(SPLIT(A1, "TZ"))

    enter image description here

    enter image description here

    see: locale differences in google sheets (documentation missing pages)

    and: https://www.cl.cam.ac.uk/~mgk25/iso-time.html

    and: https://en.wikipedia.org/wiki/ISO_8601#Times

    update:

    as mentioned Z stands for UTC

    UTC ≈ GMT see: https://24timezones.com/gmt-vs-utc

    The United States has 6 timezones:

    enter image description here

    so for example, if you reside in Pacific Time Zone you are in UTC-7

    see map: https://www.timeanddate.com/time/map/

    therefore your:

    2022-04-05T12:39:34.579775Z

    is actually equal to:

    4/5/2022 5:39:34.579775

    and the formula is:

    =SUM(SPLIT(A1, "TZ", "-7:00"))

    enter image description here

    and with milliseconds:

    =TEXT(SUM(SPLIT(A1, "TZ"), "-7:00"), "m/d/e h:mm:ss.000")

    enter image description here

    or with extra precision:

     =TEXT(SUM(SPLIT(A1, "TZ"), "-7:00"), "m/d/e h:mm:ss")&REGEXEXTRACT(A1, "(\.\d+)")

    enter image description here

    and don't forget to account for the Daylight Saving system!