Alan Rezende

Convert ISO-8601 to Date in Google Sheets

  April 17, 2023
  April 17, 2023

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 .

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
  • A1 is the value to be formatted;
  • B1 is the timezone difference in your region;
  • C1 has the formula and no formatting;
  • D1 has the same formula and is formatted as Date 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"