| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Format Question | Excel Worksheet Functions | |||
| How to combine conditional format with formula ? | Excel Worksheet Functions | |||
| conditional format of data tables in charts | Charts and Charting in Excel | |||
| Conditional format | Excel Discussion (Misc queries) | |||
| Conditional Format With SUMIF | Excel Worksheet Functions | |||
Linear Mode
