Conditional formatting in Excel
September 26, 2022 5:46 PM Subscribe
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!
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!
Yes, I just tested this out and confirmed it works.
You should have two rules, in the following order.
Rule 1:
Format only cells that contain --> Blanks
Set the format to no fill, regular text.
Click "stop if true" for this rule.
Rule 2:
Format only cells that contain --> Cell values --> less than or equal to =(TODAY()-2556)
posted by quiet coyote at 6:25 PM on September 26, 2022 [7 favorites]
You should have two rules, in the following order.
Rule 1:
Format only cells that contain --> Blanks
Set the format to no fill, regular text.
Click "stop if true" for this rule.
Rule 2:
Format only cells that contain --> Cell values --> less than or equal to =(TODAY()-2556)
posted by quiet coyote at 6:25 PM on September 26, 2022 [7 favorites]
Response by poster: Thank you so much, this worked. For some reason I had to input the rules the other way around (red shading rule first, then rule for blanks) otherwise it wouldn't work but we got there!
posted by BeeJiddy at 2:39 PM on September 27, 2022
posted by BeeJiddy at 2:39 PM on September 27, 2022
This thread is closed to new comments.
For the blank ones, I'd just have another layer of formatting - formatting blanks to something not red.
posted by pompomtom at 6:18 PM on September 26, 2022 [1 favorite]