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?

 

[KRice]

Is this correct?

the month of the value in [Go Live Date] IS NOT less than the month of Today

Your formula does otherwise.

 

[JamesCanale]

I think when a TBD text is put into the 'go live date', it throws the error in the month function, breaking the formula.

 

[KRice]

James is right...I suppose you could trap the error, maybe like this:

Book1
	A	B	C
3	1/15/2022		Go Live Date
4	3/15/2022		1/15/2022
5	5/15/2022		3/15/2022
6	2/13/2022		5/15/2022
7			6/15/2022
8			7/15/2022
9			TBD
10			8/15/2022
11			2/13/2022
Sheet2

Cell Formulas
Range	Formula
A3:A6	A3	=FILTER(tblActiveProjects[Go Live Date],(tblActiveProjects[Go Live Date]<>"TBD")*(IF(ISERROR(MONTH(tblActiveProjects[Go Live Date])),"",MONTH(tblActiveProjects[Go Live Date]))<MONTH(TODAY()))*(YEAR(tblActiveProjects[Go Live Date]=YEAR(TODAY()))))
Dynamic array formulas.

 

[rizzo93]

Thanks, James and KRice.

Yeah, I should have said Month of value is >= to Month of Today.

When I used KRice's formula and made the change to the Month portion, it did spill the dates, but not exactly filtered as expected.

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

The values in my source are:
6/8/21
12/10/21
4/12/22
5/16/22
6/10/22
6/13/22
6/13/22
6/15/22
6/20/22
6/30/22
7/1/22
7/26/22
10/31/22
TBD
TBD

(I may have to just replace TBD with blanks.)

These are the results of the formula:
6/8/21
12/10/21
6/10/22
6/13/22
6/13/22
6/15/22
6/20/22
6/30/22
7/1/22
7/26/22
10/31/22

The dates in red should not show up, but I don't understand why they're not filtering out when the other two dates did.

Any thoughts?

 

[KRice]

That's better yet, Fluff...leaving only one issue to resolve regarding the discrepancy between the initial statement and formula.

 

[Fluff]

If you are trying to get all dates from this month onwards how about

=FILTER(tblActiveProjects[Go Live Date],(tblActiveProjects[Go Live Date]<>"TBD")*(tblActiveProjects[Go Live Date]>EOMONTH(TODAY(),-1)))

 

[Fluff]

If you want just this year from this month on

=FILTER(tblActiveProjects[Go Live Date],(tblActiveProjects[Go Live Date]<>"TBD")*(tblActiveProjects[Go Live Date]>EOMONTH(TODAY(),-1))*(tblActiveProjects[Go Live Date]<DATE(YEAR(TODAY())+1,1,1)))

That's better yet, Fluff

I deleted my previous reply, as it was wrong.

 

[KRice]

I missed something earlier in Post #1...I just noticed there is a misplaced right parentheses that never properly closes the YEAR function operating on [Go Live Date]. Not that it matter because "TBD" throws an error in the YEAR function too, so Fluff's idea to avoid MONTH and YEAR being applied to [Go Live Date] makes more sense.

 

[KRice]

To follow up, I believe the answer to the earlier question about those other dates showing is because I carried the misplaced right parentheses through in my answer...and that's not correct. Extracting just that portion, I had

YEAR(tblActiveProjects[Go Live Date]=YEAR(TODAY()))

...which evaluates to {1900;1900;1900;1900;1900;1900;1900;1900;1900;1900;1900;1900;1900;1900;1900}.
So to follow the error trapping approach, another clunky IF statement would be used for the YEAR function as shown in B3. But Fluff's formula in C3 is a better way to go.

Book1
	A	B	C	D	E
2	Post #4 (incorrect)	Error trapped	Fluff's post #8		Go Live Date
3	6/8/2021	6/10/2022	6/10/2022		6/8/2021
4	12/10/2021	6/13/2022	6/13/2022		12/10/2021
5	6/10/2022	6/13/2022	6/13/2022		4/12/2022
6	6/13/2022	6/15/2022	6/15/2022		5/16/2022
7	6/13/2022	6/20/2022	6/20/2022		6/10/2022
8	6/15/2022	6/30/2022	6/30/2022		6/13/2022
9	6/20/2022	7/1/2022	7/1/2022		6/13/2022
10	6/30/2022	7/26/2022	7/26/2022		6/15/2022
11	7/1/2022	10/31/2022	10/31/2022		6/20/2022
12	7/26/2022				6/30/2022
13	10/31/2022				7/1/2022
14					7/26/2022
15					10/31/2022
16					TBD
17					TBD
Sheet2

Cell Formulas
Range	Formula
A3:A13	A3	=FILTER(tblActiveProjects[Go Live Date],(tblActiveProjects[Go Live Date]<>"TBD")*(IF(ISERROR(MONTH(tblActiveProjects[Go Live Date])),"",MONTH(tblActiveProjects[Go Live Date]))>=MONTH(TODAY()))*(YEAR(tblActiveProjects[Go Live Date]=YEAR(TODAY()))))
B3:B11	B3	=FILTER(tblActiveProjects[Go Live Date],(tblActiveProjects[Go Live Date]<>"TBD")*(IF(ISERROR(MONTH(tblActiveProjects[Go Live Date])),"",MONTH(tblActiveProjects[Go Live Date]))>=MONTH(TODAY()))*(IF(ISERROR(YEAR(tblActiveProjects[Go Live Date])),"",YEAR(tblActiveProjects[Go Live Date]))=YEAR(TODAY())))
C3:C11	C3	=FILTER(tblActiveProjects[Go Live Date],(tblActiveProjects[Go Live Date]<>"TBD")*(tblActiveProjects[Go Live Date]>EOMONTH(TODAY(),-1))*(tblActiveProjects[Go Live Date]<DATE(YEAR(TODAY())+1,1,1)))
Dynamic array formulas.