Forum Discussion
Problems comparing dates
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.
3 Replies
=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.
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.
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.
