Formula Question

R

Rachel

How do I automatically get the font color to change if I have a date that is
over 18 months old.....like I have February 1 2006 I want that to change to
Red because it is over 18 months old.

Thanks
 
S

Sandy Mann

Format > Conditional Formatting > select Formula Is form the dropdown and
enter:

=DATEDIF($G$4,TODAY(),"m")>=18

Change the $G$4 to your own reference

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Rachel

Thank you Sandy...that worked...I just realized though I need to color code
different if its over 6 months as well as under can that be added to the
formula to?

example

Feb 1, 2006 over 18months needs to be Red
May 06 over six months needs to be blue
anything under 6 months needs to be green

Thanks
 
D

David Biddulph

Format/ Conditional Formatting:
Cell Value is/ Less than: =TODAY()-(365*1.5) or
Formula is: =DATEDIF(A1,TODAY(),"m")>=18

Choose your font colour.
 
S

Sandy Mann

You can have three levels of Conditional formatting. Use the same formula
with different >= values and colours

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

David Biddulph said:
Cell Value is/ Less than: =TODAY()-(365*1.5)
Click to expand...

Very minor point but (365*1.5) is 547.5 so the colour would change during
the day. Perhaps:

=DATE(YEAR(TODAY())-1,MONTH(TODAY())-6,DAY(TODAY()))

or INT(365*1.5)

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

Did you have the over 18 moths first then the over 6 months? You don't
actually need an under 6 months because that will be the colour that you set
the cell to.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Rachel

Got It!!!! I used =TODAY()-(365*1.5) formula and I was able to get them all
the change the correct color just change 1.5 to .5 and 0...THANKS SO MUCH!
 
S

Sandy Mann

Glad that you got it but in that case the thanbks go to David.

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top