Include DATE function into FILTER function

Question

I am using Google Sheet's filter function as below

=FILTER(I2:P,REGEXMATCH(I2:I, X1),W2:W>=Y1,W2:W<=Z1)

where the W column has date info.

To get the W column I process column A with the following function

=date(right(A2,4), mid(A2,4,2),left(A2,2))

This is because The A column has the date in a format that breaks my filter as shown below.

17.06.2023
17.06.2023
16.06.2023

Is it possible to insert the Date function into the FILTER function directly. This would mean that I wouldn't have to create the column W.

Any help would be appreciated

Answer 1

I am not familiar with the whole picture of your formula specifically your filter function but how about this alternative:

By using datevalue and substitute function:

For example: on your given date: 17.06.2023 using substitute

=SUBSTITUTE(A3:A5, ".", "/") 

this will be 17/06/2023

then make it serial using datevalue which will output a number 45094,

=DATEVALUE(SUBSTITUTE(A3:A5, ".", "/"))

Assuming that, Col B is your Y1 in this example or your desired criteria then you may insert this directly in the filter function like this:

=FILTER(E3:E5,DATEVALUE(SUBSTITUTE(A3:A5, ".", "/")) >= B3)

RESULT:

Note/s:

• The Illustrated filter formula is not exactly like the posted formula but to show alternative how to insert date in filter function
• DateValue() may not recognize date so be careful with this,as the date format is based on the locale setting in your document.

Ref:

DATEVALUE

Date string cannot be parsed into date/time in Google Sheets

Sheets not recognizing dates :Error DATEVALUE parameter