If formula

[kumar0318]

Hello- I have a date column in column A, and I need to build an "IF" formula based on the following conditions:

0- days if its future date
1- If its less than 30 days
2 - if its less than 60 days
3- If its less than 120 days
4- if its more than 120 days

 

[Joe4]

OK, try this formula for a value in cell A1:

=IF(TODAY()-A1>120,4,IF(TODAY()-A1>60,3,IF(TODAY()-A1>30,2,IF(TODAY()-A1>0,1,0))))

 

[kumar0318]

Thank you!

 

[snjpverma]

Alternatively, you can try this:

=MIN(4,MAX(0,CEILING((TODAY()-A1)/30,1)))

 

[snjpverma]

Alternatively, you can try this:

=MIN(4,MAX(0,CEILING((TODAY()-A1)/30,1)))

Oops, I just noticed that it's not a perfect gap of 30 days. Hence, this formula won't work.

The below formula might work instead.

=LOOKUP(TODAY()-A1,{0,30,60,120,9999},{0,1,2,3,4})

 

[Joe4]

Oops, I just noticed that it's not a perfect gap of 30 days. Hence, this formula won't work.

The below formula might work instead.

=LOOKUP(TODAY()-A1,{0,30,60,120,9999},{0,1,2,3,4})

Your formula is off by one "shift", but it can be fixed (I think you missed the part about future dates).

My formula also might require one minor edit. If the current date should return "1" and not "0", then:

=IF(TODAY()-A1>120,4,IF(TODAY()-A1>60,3,IF(TODAY()-A1>30,2,IF(TODAY()-A1>0,1,0))))

would need a minor change like this:
=IF(TODAY()-A1>120,4,IF(TODAY()-A1>60,3,IF(TODAY()-A1>30,2,IF(TODAY()-A1>=0,1,0))))

In order to get your formula to match up with the rules in post #1, it should look like this:

=LOOKUP(TODAY()-A1,{-9999,0,30,60,120},{0,1,2,3,4})

Here is a grid with a bunch of dates, and shows what it should return and what the various formulas return.