Using FILTER to spill with certain criteria

[rizzo93]

I'm using a formula to spill values from a column in a table on one worksheet to another column on another worksheet.

The table and column is called tblActiveProjects[Go Live Date]. I want to spill the values that meet the following criteria:

• the month of the value in [Go Live Date] IS NOT less than the month of Today
• the year of the value in [Go Live Date] IS EQUAL to the year of Today
• the value in [Go Live Date] IS NOT equal to "TBD"

This is what I have so far, but it's only producing a #VALUE error:

=FILTER(tblActiveProjects[Go Live Date],(tblActiveProjects[Go Live Date]<>"TBD")*(MONTH(tblActiveProjects[Go Live Date])<MONTH(TODAY()))*(YEAR(tblActiveProjects[Go Live Date]=YEAR(TODAY()))))

If I leave it at just not equaling TBD, I'm fine, but when I get into the month and year stuff, that's where it breaks down.

Is what I want possible?

 

[rizzo93]

You all have been so helpful!

Fluff's formula worked but then I realized later down the road that I may end up with more results than I have space for in my report.

I only need data for 17 weeks out, so I enter my start date in $C$5 (a Sunday) and the formula looks only that far:

=FILTER(tblActiveProjects[ID],(tblActiveProjects[Go Live Date]<>"TBD")*(tblActiveProjects[Go Live Date]>=$C$5)*(tblActiveProjects[Go Live Date]<$C$5+(17*7)-7))

Thank you for your help, everybody. Much appreciated!

 

[Fluff]

Glad we could help & thanks for the feedback.