Date color change if date exceeds 14 days

Greeting,

I have a excel worksheet I use to track clients. I see them every two weeks. I have a column labled "Last Contacted" each time I communicate with client in any form I change the date to the current date. Sometimes for various reason client don't make appointments, or no contact has been done in a while, so what I'm looking at doing is when the current date is greater than 14 days since the client was last contacted I would like the text or date to change to maybe a light color like a pale red and if it's more than 21days to change to a bright red to warn me that this client needs to be contacted.

I know it can be done, I've seen it but just don't know how it was done and the person that was using the formula isn't around anymore. Can anyone help me please. Thanks in advance.

if dates are in column A then Highlite column A and conditional format and formula is
=today()-$A1<=14 and format font colour
formula 2 =(today()-$A1)*$A1 and format font colour

You could use Conditional Formatting from Format menu.
Select all cells with dates, select Conditional Formatting from Format menu, in the first box select 'Formula is', paste this formula:
=AND(NOW()-A1>=14,NOW()-A1<=20)
click on Format button and choose a color.

Then click on Add to add condition2, select 'Formula is' and paste this formula:
=NOW()-A1>=21
click on Format button and choose another color.

The cell 'A1' in the formula must be changed with the first date range. If your date range is E2:E10 then A1 must be changed in E2.

Regards,
Antonio

Hi and welcome to the board

Use conditional formatting with following conditions and selecting the range to format

=and(today()-$A1>=14,today()-$A1<21) for your first condition ( pale red)
and =today()-$A1>=21 as second condition ( bright red)

=AND(NOW()-A1>=14,NOW()-A1<=20)

Antoka, the NOW function will give you date AND time ( 12/04/2010 04:15:00) so the results might not be quite correct

Right, I didn't think to it, thanks!

Regards,
Antonio

Thanks to everyone for your help