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 .
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 as
The regex replaces the letters T and Z with a space.
+ 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"