CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
confuseddesk
confuseddesk

Reputation: 85

Is it possible to get Sheets to recognize my REGEXEXTRACT results as a date?

I have a column filled with date data in the following format: [Date: 09-Nov-2020 10:29 AM]

I've used the following formula to take out only the date info: =ArrayFormula(REGEXEXTRACT(C2:C,".*: (.*)\S\D")) (where C2:C is the column with the unformatted dates).

This gets the dates to display as 09-Nov-2020, but this doesn't seem to be recognized by Google Sheets as a date. When I try to edit the number formatting, nothing changes.

Oddly, I have a Data Validation list based on these shortened dates, and when I change the number formatting there it updates properly.

I'm trying to set it up so I can use a MATCH formula to locate the date based on this DD-Mmm-YYYY format, but since Sheets doesn't see the result of the REGEXEXTRACT as a date, I can't find a formula that works to do that (including MATCH, FILTER, etc.)

Unfortunately I can't directly share the sheet because of sensitive information stored on it, but based on the formula above is there something else I can do to make this work?

Upvotes: 0

Views: 125

Answers (2)

Iamblichus
Iamblichus

Reputation: 19349

While the formula provided by Rocky can be used to retrieve the day part of the date, it doesn't keep the time: 09-Nov-2020 10:29 AM becomes 09-Nov-2020 00:00 AM.

That's because TEXT is used to format to dd-mmm-yyyy, which doesn't contain the time part.

There's no need to use TEXT, though, and VALUE can be used directly to transform the string returned by REGEXEXTRACT to a date:

=ArrayFormula(IFNA(VALUE(REGEXEXTRACT(C2:C,".*: (.*)\S"))))

enter image description here

Note:

  • IFNA is used here to ignore the empty cells in C.

Reference:

Upvotes: 1

Rocky
Rocky

Reputation: 1000

Try below formula:

=value(Text(ArrayFormula(REGEXEXTRACT(C2:C,".*: (.*)\S")),"dd-mmm-yyyy"))

Upvotes: 1

Related Questions