#1
|
|||
|
|||
Help me with simple IF stmt to test date within a range
I'm embarrassed! I should know how to do this but I fergit!
I have a simple Excel database containing a column of dates. I want to test a date in any cell in that column and return "yes" in the adjacent cell if the date I'm testing is within a range of dates I'll input manually into the IF statement, and "no" if the date I'm testing is not within the range of dates I'll input manually into the IF statement. Let's say cell A3 contains the date 2/21/2014. I want cell A4 to return "yes" if the date in A3 is >= 10/1/2013 and <= 12/31/2014, and I want cell A4 to return "no" if the date in A3 is either before 10/1/2013 or after 12/31/2014. Since the AND argument does evaluate to "true" in this case, at least to my logical eye, I should get "yes" in cell A4, not "no". When I type cell A4 =IF(AND(A3>=10/1/2013,<=12/31/2014),"yes","no") I get "no" in cell A4 regardless of what date values I type in manually in the IF and AND arguments. If I simply enter dates 10/1/2013 and 12/31/2014 as dates in their own cells, and use those cell references in the argument for AND, it works. But I'd prefer not to do it that way. Is there a way to make Excel treat the character string 10/1/2013, for example, as a date in the AND argument, and not as text or numbers, which it appears to be doing? Or must I simply enter the desired dates as dates in their own cells, etc.? |
#2
|
||||
|
||||
You could use:
=IF(OR(A3<DATE(2013,10,1),A3>DATE(2014,12,31)),"NO ","YES")
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks!
Thank you! I didn't think of the OR statement. And I didn't know how to enter the year,month,day separated by commas either, so that Excel would interpret the info as a date! This does it for me!
|
#4
|
||||
|
||||
Actually statements like A3>="10/1/2013" also work
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
||||
|
||||
But, in this case, only on a PC whose regional settings use the MM/DD/YYYY format! Move to a PC using DD/MM/YYYY settings and you'll get 'interesting' results. The DATE function is impervious to such subtleties...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
test | Cheekychino | PowerPoint | 1 | 04-08-2013 09:09 PM |
Write Test | dkub | Word VBA | 1 | 07-21-2012 09:01 PM |
Date Range Prompt | MikeWooZ | Excel | 6 | 12-04-2011 02:10 PM |
Vba, Is there a better way to test empty cells? | Hwyn | Excel Programming | 2 | 11-14-2011 11:44 AM |
Test after presentation? | barnkat | PowerPoint | 0 | 08-13-2010 10:58 AM |