Conditional Format Formula Needed

[Guest]

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

 

[Guest]

I must be doing something wrong. It turned the entire column a color.

 

[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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[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

 

[Guest]

absolute reference does not make any difference - does not apply format as
needed

 

[Guest]

does not make any difference if absolute reference is used, cell does not
change color

 

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

 

[Guest]

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)

 

[Guest]

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.