Problems comparing dates

[RossRichardson]

I haven't been able to get my formula to filter dates that are greater than a start date and less than an end date to return any values.  I have formatted the values as dates.

This is the source worksheet.  The filter function should be returning any records from columns AB through AH where column AD has a value of "C", "RW" or "LW" and where the date (column AN) is between a start and end date.  I have used the DATE function to concatenate the dates to try and ensure they are in data format.  The dates in this table start at 2021-10-12 and run through 2022-02-16.

This sheet contains the filter function and uses cells B1 and B2 as the start and end dates.  I have used the same approach here to try and use the DATE function to concatenate the dates so that Excel recognizes them as a date.

[#2]

@RossRichardson 

=FILTER(AB2:AH14,((AD2:AD14="C")+(AD2:AD14="RW")+(AD2:AD14="LW"))*(AN2:AN14>=AR2)*(AN2:AN14<=AR3))

This formula seems to work in my spreadsheet. Start date and End date are in cells AR2 and AR3. 

[#3]

@OliverScheurich Thank you for this.  I notice that the date format you are using is DD.MM.YYYY.  The function I am using has a format of DATE(YYYY,MM,DD).  The date format must be a regional setting in Excel, but I wonder if it is contributing to my error.  

[#4]

@RossRichardson 

The reason for the NA error is most likely the different size of the arrays in your formula e.g. AC2:AC30752 and AN2:AN50000.

In my formula all arrays have the same size and it works perfectly.

=FILTER($AB$2:$AH$14;(($AD$2:$AD$14="C")+($AD$2:$AD$14="RW")+($AD$2:$AD$14="LW"))*($AN$2:$AN$14>=$AR$2)*($AN$2:$AN$14<=$AR$3)*($AC$2:$AC$14=AS2))

In my file i changed the format of the date and the FILTER formula still works as intended.