Conditional formatting in Excel

Post date: 2022-09-27 13:46:53

Hi all. I'm compiling an Excel spreadsheet which tracks the publication date of certain documents. I'm not particularly skilled at using Excel but can usually Google myself out of any issues, or into solutions. However, I'm having some trouble executing conditional formatting on the date of publication and I'd love some help to figure out how to make it work a little more elegantly.

Firstly, I am not sure what info y'all exactly need to know to help so am happy to answer questions as necessary.

The goal of the conditional formatting is that any date equal to or older than 7 years, based on today's date, will trigger red shading in the cell to alert whoever is looking at the sheet that the document needs updating.

The date format is your standard DD/MM/YYYY, though is set to custom display as MMM-YYYY e.g. Apr-2004. I would be happy to change the date custom display if this would help.

It's my understanding that I should be able to execute a rule that uses a formula to determine which cells to format by using the date. I spent a very generous amount of time reading up about it and trialling different formulas, but it never resulted in red shaded cells. Please don't ask me what formulas I used; I've completely forgotten. But I tried about 4 without luck.

So now I have a rule that formats only cells that contain a cell value less than a date which is defined in a cell off to the side by the formula =(TODAY()-2556). It works (results in red shading) but it has two issues. Firstly, I don't love that I must anchor it to a cell formula because someone could delete it accidentally. Secondly, it displays blank cells as red. I also spent some time trying to fix the shaded blank cell issue as I'm aware you can modify rules to get rid of this problem but didn't get anywhere.

So, my question(s): Does anyone know how to form a rule to determine cell formatting for dates equal to or older than 7 years, that doesn't require referring to a cell on the sheet? Or, does anyone at least know how to stop blanks as being formatted with red shading?

Thanks so much!

Please click Here to read the full story.