Forum Discussion
RossRichardson
Feb 22, 2022Copper Contributor
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 ...
RossRichardson
Feb 23, 2022Copper Contributor
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.
OliverScheurich
Feb 23, 2022Gold Contributor
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.