Formula for identifying a date between two others

I want a formula in cell $B1 which will look at a date in $A1 and put a message in $C1. If the date in $A1 is equal to or less than 30 days before TODAY(), the message in $C1 should read "GREEN". If the date in $A1 is between 31 and 60 days before TODAY(), the message in $C1 should read "AMBER". If the date in $A1 is 61 days or more earlier than TODAY(), the message in $C1 should read "RED".

I can do the first part; I can do the third part; it is the "between" in the middle section which is making my head hurt. I tried AND, as in if it is more than 31 AND less than 60 but I just can't get the syntax right. Any help gets you on my Christmas list! BTW, I am using Excel 2013 but this formula may be used by someone with 2010 too, so it would need to work on both.

Many thanks in anticipation of what is probably a simple solution, but one which escapes me.

regards

You dont need a middle bit, just order them from high to low.

=if(a1>=today()+61,"red",if(a1>=today()+31,"amber","green")

Explanation:

If a1 >= today()+61 then red

if a1 >= today() + 31 it must also be <=today()+61 otherwise we would have already assigned red as the result.

if the result is neither red nor amber then a1 must be < 31 so assign green.

Change your subject title (dont ask me how) before a mod comes along...

Rule 1

http://www.excelforum.com/forum-rule...rum-rules.html

Hi Struggling Senior, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )