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

Question

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?

Articulate

That's the international ISO format. – Walloping

answer updated.. – Illiberal

Answer 1

try:

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

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:

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

and with milliseconds:

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

or with extra precision:

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

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

Illiberal

Answer 2

You can try

date('Y-m-d h:i:s', strtotime($yourDate));

Champagne

Thanks Annisa, I'm not quite sure how to make this work though – Articulate