Conditional Format Formula Needed

P

PAR

I would like to have a cell turn a color when the value of the following
formula is a multiple of 5.

Year(today())-Year(b2)

b2 = date of hire

Thank you
 
C

Chip Pearson

Try the following formula:

=MOD(YEAR(TODAY())-YEAR(B2),5)=0


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
B

bj

if the same color is acceptable for each multiple of 5
try
if(A3<>"",(mod((year(today())-year(A3)),5)=0)
and set your format
 
B

BorisS

in the conditional formatting, select formula instead of value and use
=mod(Year(today())-Year(b2),5)=0, then set your formatting.

MOD gives you the remainder when one number is divided by another.
 
P

PAR

Does not work. Is it because I have the formula year(today())-Year(f3) as
the contents of the cell?

I need to have the same color for all multiples of 5 years. So if the
answer to year(today())-Year(f3) = 5, or 10, or 15, or 20, or 25, or 30, or
35,etc the color of the font or pattern changes
 
C

Chip Pearson

You should use absolute referencing.
=MOD(YEAR(TODAY())-YEAR(B2),5)=0
should be
=MOD(YEAR(TODAY())-YEAR($B$2),5)=0


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
C

Chip Pearson

It works for me. Are you sure you have the proper cell selected
when you apply conditional formatting, and are you sure you
actually specified a format?
 
B

bj

I assume F3 is the cell with the start year in it

in a blank cell enter
=mod(year(today())-year(F3),5)
in another blank cell enter
=mod(year(today())-year(F3)-1,5)
are the answers what you expect?
I am wondering if your F column is text rather than dates.
if they are, try
if(F3<>"",(mod((year(today())-year(value(F3))),5)=0)
 
P

PAR

Yes, I have the correct cell selected and a format.

Whether I use the absolute reference or not all the cells are formated with
the conditional format, not just the 5 year incremental dates.
 
Top