Evaluate a date range and return a value

[StartingOut]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have workbook with about 500 rows using column A and B, column A has a date and column B has lust a product name. I need to evaluate the date. The evaluation is to check column A for the date and if it is between certain days, place a number value in column C<o></o>
<o> </o>
0 to 1 day old = 1 in column C<o></o>
2 to 5 days old = 2 in column C<o></o>
6 to 10 days old= 6 in column C<o></o>
11 to 20 days old = 11 in column C<o></o>
21 to 30 days old = 21 in column C<o></o>
31 to 60 days old = 31 in column C<o></o>
More than 60 days = 60 in column C<o></o>

 

[Desu Nota from Columbus]

0 to 1 day old = 1 in column C
2 to 5 days old = 2 in column C
6 to 10 days old= 6 in column C
11 to 20 days old = 11 in column C
21 to 30 days old = 21 in column C
31 to 60 days old = 31 in column C
More than 60 days = 60 in column C

Place =today() in cell ZZ10000:

=IF(AND(ZZ10000-A1>60,C1=60,IF(AND(ZZ10000-A1>=0, ZZ10000-A1<=1),C1=1,IF(AND(ZZ10000-A1>=2, ZZ10000-A1<=5),C1=2,IF(AND(ZZ10000-A1>=6, ZZ10000-A1<=10),C1=6,IF(AND(ZZ10000-A1>=11, ZZ10000-A1<=20),C1=11,IF(AND(ZZ10000-A1>=21, ZZ10000-A1<=30),C1=21,IF(AND(ZZ10000-A1>=31, ZZ10000-A1<=60),C1=31,""))))))))

Not sure the end parenthesis are correct! Adjust as necessary. There has to be a better way to do this, but this should work.

 

[Jonmo1]

Try

=LOOKUP(TODAY()-A1,{0,2,6,11,21,31,61},{1,2,6,11,21,31,60})

 

[Desu Nota from Columbus]

Jonmo1,

Thanks for making me feel 'special' ...

 

[Jonmo1]

No worries...

It's all about learning right?

Anytime you start nesting several if's, it's likely some sort of lookup will work...

Jonmo1,

Thanks for making me feel 'special' ...

 

[StartingOut]

Hi, this on its own didn't work and returned an error.

=IF(AND(ZZ10000-A1>60,C1=60,IF(AND(ZZ10000-A1>=0, ZZ10000-A1<=1),C1=1,IF(AND(ZZ10000-A1>=2, ZZ10000-A1<=5),C1=2,IF(AND(ZZ10000-A1>=6, ZZ10000-A1<=10),C1=6,IF(AND(ZZ10000-A1>=11, ZZ10000-A1<=20),C1=11,IF(AND(ZZ10000-A1>=21, ZZ10000-A1<=30),C1=21,IF(AND(ZZ10000-A1>=31, ZZ10000-A1<=60),C1=31,""))))))))


And I am not sure where to place the Lookup formula.

 

[Desu Nota from Columbus]

Use the lookup in column c where you want the values returned. put the formula in every row where there is data in A

 

[StartingOut]

Ok I figured out wher to place the Loo Up its in ZZ10000 now I am getting "your formula contains an error" message" then I try taking out 1 ) then I get the message your formula is missing --) or (.

 

[StartingOut]

I stand corrected then I didn't figure out where to place the Look up

 

[Desu Nota from Columbus]

=LOOKUP(TODAY()-A1,{0,2,6,11,21,31,61},{1,2,6,11,21,31,60})


all you need is this formula. Put this in column C.

My formula (with the 8 if functions) is an archaic and superfluous version of the formula above!