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.
I have the rule for yearly which is =NOW()
But i don`t know how to flag it when its every 2, 3 or four years.
| 05/10/2017 |
| 02/08/2014 |
| 26/05/2018 |
| 21/02/2017 |
- 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>)+1I 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:
