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`

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