Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
PAR
 
Posts: n/a
Default 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
  #2   Report Post  
PAR
 
Posts: n/a
Default

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

  #3   Report Post  
Chip Pearson
 
Posts: n/a
Default

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



  #4   Report Post  
bj
 
Posts: n/a
Default

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

  #5   Report Post  
BorisS
 
Posts: n/a
Default

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



  #6   Report Post  
PAR
 
Posts: n/a
Default

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

  #7   Report Post  
Chip Pearson
 
Posts: n/a
Default

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





  #8   Report Post  
PAR
 
Posts: n/a
Default

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






  #9   Report Post  
PAR
 
Posts: n/a
Default

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






  #10   Report Post  
Chip Pearson
 
Posts: n/a
Default

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









  #11   Report Post  
bj
 
Posts: n/a
Default

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

  #12   Report Post  
PAR
 
Posts: n/a
Default

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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format Question DougS Excel Worksheet Functions 3 May 3rd 05 01:36 AM
How to combine conditional format with formula ? merlin68 Excel Worksheet Functions 4 April 14th 05 03:24 AM
conditional format of data tables in charts [email protected] Charts and Charting in Excel 2 January 25th 05 04:56 PM
Conditional format rexmann Excel Discussion (Misc queries) 4 December 2nd 04 01:01 PM
Conditional Format With SUMIF Minitman Excel Worksheet Functions 3 November 1st 04 03:58 PM


All times are GMT +1. The time now is 09:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"