If date greater than 5 years, fill cell with Yes otherwise with No

[The CMF]

Hi,
I managed to get conditional formatting on colomn B which works great.
=B1<=DATE(YEAR(TODAY())-5;MONTH(TODAY());DAY(TODAY()))

However I need a formula that will fill the column A: If date is greater than 5 years, then results should be Yes otherwise No.

Can someone help me out with that?
Thanks
Raoul

 

[Toadstool]

Hi TheCMF

This should work.

Book1
	A	B
1	Yes	5/21/2012
2	Yes	3/8/2016
3	Yes	11/5/2013
4	No	6/7/2016
5	No	4/1/2019
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=IF(EDATE(B1,60)>TODAY(),"No","Yes")

 

[arthurbr]

Essentially the same

=IF(B1<=DATE(YEAR(TODAY())-5;MONTH(TODAY());DAY(TODAY()));"yes";"No")

Example with semi colons as per yours

 

[The CMF]

Perfect!
Thanks

Hi TheCMF

This should work.

Book1
	A	B
1	Yes	5/21/2012
2	Yes	3/8/2016
3	Yes	11/5/2013
4	No	6/7/2016
5	No	4/1/2019
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=IF(EDATE(B1,60)>TODAY(),"No","Yes")

 

[The CMF]

That one is great too. Thanks a lot!

Essentially the same

=IF(B1<=DATE(YEAR(TODAY())-5;MONTH(TODAY());DAY(TODAY()));"yes";"No")

Example with semi colons as per yours

 

[The CMF]

Hi Toadstool

Thanks for your reply, I forgot to mention that sometimes there is no date, just a blank cell. Using the formula it shows "Yes" when the date cell is blank.
How can avoid this?
Thanks!

Hi TheCMF

Hi TheCMF

This should work.

Book1
	A	B
1	Yes	5/21/2012
2	Yes	3/8/2016
3	Yes	11/5/2013
4	No	6/7/2016
5	No	4/1/2019
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=IF(EDATE(B1,60)>TODAY(),"No","Yes")

This should work.

Book1
	A	B
1	Yes	5/21/2012
2	Yes	3/8/2016
3	Yes	11/5/2013
4	No	6/7/2016
5	No	4/1/2019
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=IF(EDATE(B1,60)>TODAY(),"No","Yes")

 

[Toadstool]

You could just add another IF

TheCMF.xlsx
	A	B
1	Yes	5/21/2012
2
3
4	Yes	3/8/2016
5
6
7	Yes	11/5/2013
8	No	6/7/2016
9	No	4/1/2019
10
Sheet1 (2)

Cell Formulas
Range	Formula
A1:A10	A1	=IF(ISNUMBER(B1),IF(EDATE(B1,60)>TODAY(),"No","Yes"),"")

 

[Catmeow1]

Hi TheCMF

This should work.

Book1
	A	B
1	Yes	5/21/2012
2	Yes	3/8/2016
3	Yes	11/5/2013
4	No	6/7/2016
5	No	4/1/2019
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=IF(EDATE(B1,60)>TODAY(),"No","Yes")

Hi, first of all, thanks for the formula! It works! But how can i replace the 60 with a column of months data that i have.

Instead of 5 years, i have a column of different year values.