Conditional formatting between date ranges

  • Thread starter Thread starter Carl D. Zimmerman
  • Start date Start date
C

Carl D. Zimmerman

I want to use conditional formatting for a column of
employee hire dates. I want one format to reflect a new
hire (today's) and one year prior (ex Bold Red letters).
Another format I want to show one year prior to today and
two years prior to today's date (ex Bold Black letters w/
blue background). As time passes, a new hire will
automatically go from Red bold Letters to bold black
letters w/ blue background to no special formatting
("normal").

I can't figure out how to use 'Today()' or 'Now()' into a
conditional format formula that makes this work.
 
Condition 1:

Formula is:
=$B2>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

Condition 2:

Formula is:
=$B2>DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY()))

You may run into a few "glitches" when leap years come into play, but this
should work for the most part.
 
It worked except for the employees beyond two years
experience. Instead of having no formatting, they were
also formatted like the employees between 1 yr and 2 yrs
experience. From everyone 2yrs+, I want to show no
special conditional formatting for those entries.

Thanks for your help so far.

Carl
 
Back
Top