dategoogle-sheetssplitstring-formattinginformation-extraction

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

  • try:

    =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