If function to evaluate if date is today or six months back

[S Himmelrich]

I've created the following function, which is not working. I'm trying
to evaluate cell C2 (date) to identify if it is today() or six months
back and return yes if it is or no if it isn't. I don't have a syntax
error, but the following statement is not returning correct
information - please help:

=IF(AND(C2 = TODAY(),C2 < DATE(YEAR(NOW()),MONTH(NOW())-6,0)), "Yes",
"No")

Thank you!

 

[Claus Busch]

Hi,

Am Wed, 18 Jan 2012 09:26:28 -0800 (PST) schrieb S Himmelrich:

=IF(AND(C2 = TODAY(),C2 < DATE(YEAR(NOW()),MONTH(NOW())-6,0)), "Yes",
"No")

try:
=IF(OR(C2=TODAY(),C2<=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),"Yes","No")


Regards
Claus Busch

 

[S Himmelrich]

Hi,

Am Wed, 18 Jan 2012 09:26:28 -0800 (PST) schrieb S Himmelrich:


try:
=IF(OR(C2=TODAY(),C2<=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),"Y­es","No")

Regards
Claus Busch

I get a #REF! error when I use this suggestion

 

[S Himmelrich]

Hi,

Am Wed, 18 Jan 2012 09:26:28 -0800 (PST) schrieb S Himmelrich:


try:
=IF(OR(C2=TODAY(),C2<=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),"Y­es","No")

Regards
Claus Busch

This evaluation is not working acutally, I get different results - I'm
looking for the function to populate the cell with yes if = today or
within the range of six months back

 

[Claus Busch]

Hi,

Am Wed, 18 Jan 2012 11:12:26 -0800 (PST) schrieb S Himmelrich:

This evaluation is not working acutally, I get different results - I'm
looking for the function to populate the cell with yes if = today or
within the range of six months back

try:
=IF(OR(C2=TODAY(),C2>DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),"Yes","No")


Regards
Claus Busch

 

[S Himmelrich]

Hi,

Am Wed, 18 Jan 2012 11:12:26 -0800 (PST) schrieb S Himmelrich:


try:
=IF(OR(C2=TODAY(),C2>DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),"Ye­s","No")

Regards
Claus Busch

Here is the result of the second try - need from today and six months
back so (7/18/2012-1/18/2012)

2/1/2010 2/4/2010 No
4/9/2007 4/26/2007 No
4/27/2011 1/25/2012 Yes
1/17/2011 12/2/2011 Yes
1/10/2011 3/4/2011 No
4/28/2011 4/28/2014 Yes
5/4/2011 10/19/2011 Yes
1/30/2012 4/27/2012 Yes
5/1/2011 11/23/2011 Yes
5/5/2011 10/1/2011 Yes
10/3/2011 4/30/2012 Yes
5/1/2007 5/11/2007 No
5/1/2007 6/25/2007 No
6/5/2007 9/24/2007 No
5/1/2007 5/11/2007 No
4/9/2007 4/26/2007 No
5/1/2007 6/25/2007 No
3/12/2007 6/29/2007 No
5/17/2010 1/31/2011 No
3/1/2010 9/30/2011 Yes
12/23/20099/30/2010 No
8/1/2009 9/30/2011 Yes
3/24/2010 9/30/2011 Yes
3/24/2010 10/2/2010 No
1/1/2010 2/28/2010 No
12/1/2009 5/31/2010 No
1/4/2010 10/2/2010 No

 

[S Himmelrich]

That's a little different from your first question.

Try:

=IF(AND(C2<=TODAY(),C2>=EDATE(TODAY(),-6)),"yes","no")

If you have a version of excel prior to 2007, and this gives you a #NAME error, see Excel HELP for the EDATE function for instructions on installingthe Analysis ToolPak.

Thank works perfectly, thank you for hanging in there with me. Have a
great rest of our week.

 

[Ron Rosenfeld]

Thank works perfectly, thank you for hanging in there with me. Have a
great rest of our week.

Glad to help. Thanks for the feedback.