Conditional Formatting for dates

[Melissa44]

I have a spreadsheet that list all my recruiter's clients and when the last
time they had lunch with them. I am looking for a way to change the font
color depending on when the last time they had lunch with them. Here is what
I am looking for.

Year and Over: Red font
6 months to a year: Orange font
current date to 6 months: Green Font

What would be the formulas to get these to change and keep up with the most
current date (being today)

 

[Pete_UK]

Use Conditional Formatting, not worksheet functions.

Which column is your date in?

Do you want all the cells on a particular row to change colour, or
just the date column?

Pete

 

[Eduardo]

Hi Melissa,.
In cell D1 enter the formula to bring todays date
=today()
then highlight the column where you have the dates you want to highlight
with colours and do conditional formating, where the formula is enter

=($D$1-D)>180
then select the colour you want

 

[David Biddulph]

First CF condition/ Formula Is/ =DATEDIF(A2,TODAY(),"y")>=1 for your Red
Second CF condition/ Formula Is/ =DATEDIF(A3,TODAY(),"m")>=6 for Orange
Default formatting Green
(and perhaps also test for blank cells if this might be an option).

 

[Melissa44]

My date [=Today()] is in K1. The column where I need to change the colors is
E3 to E175.

If its less than 180= green
If its more than 180 but less than 360= Orange
If its more than 360 =red

 

[Melissa44]

Okay I tried that and it didnt work. Or I messed it up somehow.

Here are the example dates I have:
10/17/2008
12/23/2008
11/28/2007
12/10/2008
12/10/2008

So everything in Dec should be green (which I changed my font default to
green), 11/28/07 should turn red. So I highlighted column E because that is
the dates I want to change colors. I made my default color font green. I
click on Format>Conditonal Formatting. First my first one, I click the drop
down box and changed "Cell value" to "Formula is". Then I copy and pasted
the formula you said =DATEDIF(A2,TODAY(),"y")>=1 and changed the font color
to red. Then I added another Conditions. In Condition 2, I changed the
"Cell value" is to "Formula is" and pasted the second formula you gave me
=DATEDIF(A3,TODAY(),"m")>=6 and changed that font to orange. Then I clicked
okay and nothing happened.

In (K1) is where I have the date =Today()? Did I not do something right?

 

[Glenn]

You need to put the correct cell references in the Conditional Formatting formulas.

When you say "I highlighted column E", did you actually click on the "E" column
header, or did you select E3:E175 (your desired range per another post in this
thread)? Assuming the second, and that E3 is the currently active cell within
that selection, your CF formulas would be as follows:

=DATEDIF(E3,TODAY(),"y")>=1
=DATEDIF(E3,TODAY(),"m")>=6

K1 is not necessary.