Conditional Format Formula Needed
 

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

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

"BorisS" wrote:

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


"PAR" wrote:

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

Try the following formula:

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


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



"PAR" wrote in message
...
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

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

"PAR" wrote:

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

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


"PAR" wrote:

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

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

"bj" wrote:

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

"PAR" wrote:

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

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


"Chip Pearson" wrote in message
...
Try the following formula:

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


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



"PAR" wrote in message
...
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

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

"Chip Pearson" wrote:

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


"Chip Pearson" wrote in message
...
Try the following formula:

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


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



"PAR" wrote in message
...
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

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

"Chip Pearson" wrote:

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


"Chip Pearson" wrote in message
...
Try the following formula:

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


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



"PAR" wrote in message
...
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

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?


"PAR" wrote in message
...
does not make any difference if absolute reference is used,
cell does not
change color

"Chip Pearson" wrote:

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


"Chip Pearson" wrote in message
...
Try the following formula:

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


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



"PAR" wrote in message
...
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

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)



"PAR" wrote:

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

"bj" wrote:

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

"PAR" wrote:

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

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.


"Chip Pearson" wrote:

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?


"PAR" wrote in message
...
does not make any difference if absolute reference is used,
cell does not
change color

"Chip Pearson" wrote:

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


"Chip Pearson" wrote in message
...
Try the following formula:

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


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



"PAR" wrote in message
...
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