Formula Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
Format/ Conditional Formatting:
Cell Value is/ Less than: =TODAY()-(365*1.5) or
Formula is: =DATEDIF(A1,TODAY(),"m")>=18

Choose your font colour.
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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!
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top