Extracting date and time from cell in google sheets

Question

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

Does this answer your question? [How to conditionally change a date based on a time? Time zone differences?](https://stackoverflow.com/questions/71368583/how-to-conditionally-change-a-date-based-on-a-time-time-zone-differences) — Mike Steelson, Mar 08 '22 at 14:35

player0 · Accepted Answer · 2022-03-08T14:16:57.247

0

try:

=SPLIT(D2; "TZ")

for array:

sidenote: columns are formatted as Date and Time

for forced formatting use:

edited Mar 08 '22 at 14:16

answered Mar 08 '22 at 14:10

player0

1

Thank you very much! The array works like a charm! You made my day! – Hayo Mar 08 '22 at 14:31

1 Answers1