Extracting date and time from cell in google sheets-CodePudding

I'm trying to extract the time and date from the following cell in google sheets: 2022-02-21T05:14:05.556Z

I've managed to extract the time with the formula: =TIMEVALUE(REGEXEXTRACT(D2,"\d :\d :\d "))

But I cannot find a formula to also get the correct date out of it.

Anyone has an idea how to do it? I'd preferably extract both date & time at the same time!

Cheers, Hayo

CodePudding user response：

try:

=SPLIT(D2; "TZ")

for array:

sidenote: columns are formatted as Date and Time

for forced formatting use: