I am trying to save work on 4000+ rows of data using the IF(AND function. Having trouble getting it to work. (I am using Google Docs Spreadsheet, btw). Using dates seems to complicate things. If the date is between two values (which will be adjacent to the next date), I want to display a number based on that range, so:

If date is between 1/3/1941 and 1/2/1943, display "77"

If date is between 1/3/1943 and 1/2/1945, display "78" and so on...I can't get this to work using nesting IF(AND, and can't even get IF(AND to work with only one range.

For instance, if the value in I2 = 11/4/1941, in I3 I have =IF(AND(I2>=1/3/1941,I2<=1/2/1943),"77","ERROR") . "ERROR" shows up, when "77" should be returned. When I try multiple IF(AND's I get an error in the cell. If I change the value in I2, I never get "77" to show up in I3.

I'm using <= and >= and am okay with using just < and > and adjusting days if that may be the issue.

Thanks for any help

The way you have your "dates" in your formula, Excel is not considering them as a date, but as a number: 1 divided by 3 divided 1941.

Try this instead:

=IF(AND(I2>=DATEVALUE("1/3/1941"),I2<=DATEVALUE("1/2/1943")),"77","ERROR")

BTW... if you are trying to determine a person's age, perhaps you should read the information found here:

http://www.ozgrid.com/Excel/calcula...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History