Extracting date and time from cell in google sheets

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

>Solution :


=SPLIT(D2; "TZ")

for array:

enter image description here

sidenote: columns are formatted as Date and Time

for forced formatting use:

enter image description here

Leave a Reply