Google Sheets: convert string to date

Question

I have to following date string:

2019-05-07T19:00:00.000Z

I now want to convert date and time into 2 cells, but it still doesnt work with the date. I tried it with:

=LEFT(F2; FIND("T"; F2) - 1)

Which results in:

2019-05-07

But I can not convert this into a date like DD.MM.YYYY. If I type "2019-05-07" manually, I can convert it - but it is not convertable with the imported data. Why?

And I have the same problem with the time.

Answer 1

Google recently added LET (and some other cool functions).

LET can help here, assuming 2019-05-07T19:00:00.000Z is in F2:

=LET(
fecha, REGEXEXTRACT(F2, "(\d+)-(\d+)-(\d+)T.*"),
yyyy,  INDEX(fecha, 1, 1), 
mm,    INDEX(fecha, 1, 2),
dd,    INDEX(fecha, 1, 3),
DATE(yyyy, mm, dd))

Answer 2

To answer your question, albeit superficially, text functions (such as LEFT and TEXT) return strings that Sheets does not attempt automatically to convert to a number that might then be presented in a Date format.

However, this can be forced with the VALUE function. Thus wrapping VALUE around what you tried, like so:

=value(LEFT(F2; FIND("T"; F2) - 1))

should, for your example, return 07.05.2019 when formatted with a custom format of

dd.mm.yyyy

Since tagged regex you could apply:

=split(regexreplace(F3;"[A-Z]";" ");" ")

to replace both the T and the Z with spaces before splitting on spaces since the format looks likely to be consistent (eg always 24 characters) but a shorter version would be:

=split(left(F2;23);"T")

with application of suitable formatting for each of the date and the time.

Applying, as in a Comment,

=MID(G2; 12; 5)

has the potential disadvatage that MID is also a Text function (so not suitable for, for example, convenient formatting with AM/PM).