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

  • Thread starter Thread starter S Himmelrich
  • Start date Start date
S

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!
 
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")
Click to expand...

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


Regards
Claus Busch
 
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
Click to expand...

I get a #REF! error when I use this suggestion
 
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
Click to expand...

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
 
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
Click to expand...

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


Regards
Claus Busch
 
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
Click to expand...

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
 
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.
Click to expand...

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