My excel statement should identify the year I want the date to be associated with. If my date in column E is less than 11/1/2015, then column F reads 2015 If the date in column E is between 10/31/2015 and 11/1/2016, then column F reads 2016 If the date in column E is greater than 10/31/2016, then column F reads 2017 However, they are all reading 2015. =IF(E2<"11/01/2015","2015",IF(AND(E2<"11/1/2016",E2>"10/31/2015"),"2016",IF(E2>"10/31/2016","2017")))

Reputation: 203

Excel "IF" "AND" statement not returning desired result

My excel statement should identify the year I want the date to be associated with.

If my date in column E is less than 11/1/2015, then column F reads 2015
If the date in column E is between 10/31/2015 and 11/1/2016, then column F reads 2016
If the date in column E is greater than 10/31/2016, then column F reads 2017

However, they are all reading 2015.

=IF(E2<"11/01/2015","2015",IF(AND(E2<"11/1/2016",E2>"10/31/2015"),"2016",IF(E2>"10/31/2016","2017")))

Upvotes: 0

Reputation: 12113

This should also work:

=INDEX({2015;2016;2017},MATCH(E2,{0;42309;42675},1),1)

I had to convert the dates to numbers so that's what the 0, 42309 and 42675 are. There might be a better way to keep them looking like dates.

If E2 is not a number then it will throw an error. You can use the IFERROR like with Scott's answer to avoid it:

=IFERROR(INDEX({2015;2016;2017},MATCH(E2,{0;42309;42675},1),1),"date is text")

Upvotes: 2

Reputation: 152495

Use this:

=IFERROR(MIN(MAX(2015,YEAR(E2+61)),2017),"date is text")

Upvotes: 2

Reputation: 9874

=IF(AND(E2<>"",NOT(ISNUMBER(E2))),"DATE IS TEXT",IF(E2<date(2015,11,1),2015,IF(E2>DATE(2016,19,31),2017,2016)))

This formula will also test if your date in E is actually a date in excel's serial date format or TEXT.

Upvotes: 3