Convert ISO-8601 to Date in Google Sheets
Related to:: Google Sheets
Turns out ISO-8601 dates are not directly supported by Google Sheets, so if we have a value like this 2023-04-17T16:29:20.085Z we can’t use DATEVALUE function to get a date value that can be formatted as we want.
We need first to transform it into a value that can be recognizable by Google Sheets like 2023-04-17 16:29:20.085 .
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2023-04-17T16:29:20.085Z | -3:00:00 | 1080805:29:20 | 4/17/2023 13:29:20 |
I found that using a regex replace is the simpler, clean, and most readable way of doing it:
=regexreplace(A1, "T|Z", " ")
And, since I’m already doing something to the date, I might also add the difference to my timezone and get a final date time value in my current region.
=regexreplace(A1, "T|Z", " ") + B1
A1is the value to be formatted;B1is the timezone difference in your region;C1has the formula and no formatting;D1has the same formula and is formatted asDate Time.
The regex replaces the letters T and Z with a space.
The + B1 part will add the time difference to get the final value in your timezone, But if you want, instead of using a cell, you can hard code it in the formula.
=regexreplace(A1, "T|Z", " ") + "-3:00"