Forum Discussion
Conditional formatting using different colours
I have a spreadsheet for work where there are dates (all different). I need to create a rule/s in conditional formatting so it highlights red when its due. Some are due every 2, 3 or four years.
...
Can you describe more how you would like to highlight for yearly, every 2, 3 or 4 years?
Do you want this?
=YEAR(NOW())-YEAR(<theDateCell>)+1
Do you want this?
=YEAR(NOW())-YEAR(<theDateCell>)+1
I would like it to highlight the cell red.
Example...if the date in the cell was 01/02/2014 and it was expired date say 3 years it would highlight red.
if that makes sense
You may try this
=AND(DATEDIF($A1,NOW(),"y"),NOW())>=1,DATEDIF($A1,NOW(),"y"),NOW())<=4)
DATEDIF function can be found:
HI thanks for all your help. Unfortunately its still not working.
I can get it to highlight when it has expired after a year using =NOW()
But i cannot get it to do the same for 2 years, 3 and four
The rule Willy gave returns TRUE if from now to start date is more than one year and less than four, and FALSE for any other dates.
If different rules (for 1,2,3 and 4 years accordingly) or number of years is set as a value (<n>) when it could be
=INT(YEARFRAC(A1,TODAY(),1)) = <n>
