IF Formula

[akowalu]

I'm trying to do an IF formula where it will look at a list of dates in column "D" and if that date is between 7/01/06 - 10/01/06 I want it to return "0-3 months".. and if the date is between 04/01/06 - 06/31/06, I want it to return "4-6 months".. and if the date is 3/31/06 or before I want it to return "6+ months". Any suggestions?

 

[Scott Huish]

First a question, do you really want to hard code those dates, or are those choices of dates based on the current month somehow?

 

[akowalu]

We can hardcode those dates or whatever makes it easier

 

[Scott Huish]

If you would have to go in and manually change those every month, I think that it would be better to have a formula that would deal with that. If that is the case, can you please share what the choice of dates is based on?

 

[akowalu]

We could manually change the dates, if need be.. But the range is always going to be set at 0-3 months; 4-6 months & 6+ months from the current date. The dates listed above are what we're currently looking at.

 

[Scott Huish]

You will need the Analysis Toolpak loaded for this, does this work for you?

=LOOKUP(DATEDIF(EOMONTH(D1,-1)+1,EOMONTH(TODAY(),0)+1,"m"),{0,"0 to 3 months";4,"4 to 6 months";7,"6+ months"})

 

[akowalu]

FANTASTIC! It works beautifully... Thanks so much!