Nested What If statement using date ranges

**Glenn1** · 01-08-2012, 01:28 PM

Hello,

I am trying to create a nested what if statement in excel using multiple date ranges. For example:

If Cell X > 12/2/2009 but less than 12/31/2011 return 0-3

If Cell X > 12/2/2006 but less than 12/2/2009 return 3-5

If Cell X > 12/2/2001 but less than 12/2/2006 return 5-10

If Cell X < 12/2/2001 return over 10

The what if statement I created rerutrns the same answer regardless what is in Cell X
=IF(J31>="12/2/2009"&J31<"12/31/2011","0-3",IF(J31>="12/2/2006"&J31<"12/2/2009","",IF(J31>="12/2/2001"&J31<"12/2/2006","5-10",IF(J31<="12/2/2001","over 10"))))

Please help

**Richard Buttrey** · 01-08-2012, 01:39 PM

Hi Glenn and welcome to the forum,

There seem to be a couple of things wrong.

1. You appear to be using literals (i.e. text strings) for your dates in the formula, whereas I suspect column J probably contains proper date numbers.
2. In any case you are not using the correct AND() syntax condition in your formula. The '&' symbol is not the same as the logical 'AND' condition.

But more importantly you would be better off using a VLOOKUP table rather than complicating your requirement with an IF formula. Build yourself a two column list with dates in the first column and the values yo require '0-3', '3-5', '5-10', 'over 10' in the second column and use the VLOOKUP to lookup a column J date in the second column of the list.

Just another observation which may be irrelevant but annoys the statistician in me. Should not the items returned be '0-3, 'over 3-5', 'over 5-10', etc.? Values like 3,5,10 seem to fit into two categories.

**Haseeb Avarakkan** · 01-08-2012, 01:41 PM

Hello Glenn,

Make 2 column table. First column enter starting level dates. say in A1:B5

Please Login or Register  to view this content.

Then use like,

=LOOKUP(J31,A1:B5)

Or without table,

=LOOKUP(J31,{0;"12/2/2001";"12/2/2006";"12/2/2009";"1/1/2012"}+0,{"Over 10";"5-10";"3-5";"0-3";""})

Assume After 1/1/2012, you don't want to return anything.