date - Excel - how can i use the Filter formula to account for the next month?

Question

i made use of the FILTER formula to bring me data that falls in the same month as TODAY: =FILTER(Data!N2:N22,MONTH(Money!C29)=MONTH(Data!M2:M22))

I'm now trying to do the exact same, except for the next month

i've been toying around with different variations of the EDATE function, but can't get it to work. I keep getting a #CALC! error: =FILTER(Data!N2:N22,EDATE(MONTH(Money!C29),1)=MONTH(Data!M2:M22))

i've also tried this formula, which does return values, but it returns data for all months in my table, as opposed to just next month's:

=FILTER(Data!N2:N22,(MONTH(EDATE(Money!C29,1)=MONTH(Data!M2:M22))))

please let me know if you would like more clarification

**edit - not really sure how to add sample data here

Data Sheet

M	N	O

question from:https://stackoverflow.com/questions/65866432/excel-how-can-i-use-the-filter-formula-to-account-for-the-next-month

Answer 1

You'd want to use EOMONTH function.

For this month, something like (where Dates refers to your column of dates):

=FILTER(myArray,(Dates>EOMONTH(TODAY(),-1))*(Dates<=EOMONTH(TODAY(),0)))

and for next month:

=FILTER(myArray,(Dates>EOMONTH(TODAY(),0))*(Dates<=EOMONTH(TODAY(),1)))

You'll have to replace myArray and Dates with whatever is appropriate for your data.