I’m writing this post in response to a comment that Charles wrote in my “How to change the color of an Excel date to red if over 1 year old” post. It was such a good comment I thought I’d share my thinking with the rest of you in a new post.
“I am tracking the training status of the people on my project. All training has to be taken annually. I want to set it up where the cell turns green if the date in the cell is between 1 and 333 days from today’s date i want to set it up where the cell turns yellow if the date in the cell is between 334 and 364 from today’s date I want to set it up where the cell turns red if the date in the cell is 365 days or more from today’s date.” – Charles
Let’s start with making the dates turn red if the training date has occurred over 1 year ago. In my screen shots below I’m using date cells that start with B2 so I select the first date all the way down to the last date. I click Home tab–>Conditional Formatting –>New Rule. Then I choose “Use a formula to determine which cells to format.”
I click format and select a red fill and then I write the formula.
The TODAY() function is volatile and updates to the current date. Excel has a serial number value for each date. For example Jan 1, 1900 =1, Jan 2, 1900 =2, Jan 3, 1900 =3, Jan 4, 1900 =4 and Sep 19, 2014 =41,901. Everyday is another plus one. So we can use the greater than > and less than < operators to compare dates against the TODAY() function. You have to think in numbers and older dates will always be lesser than newer dates. So if you were born a long time ago you actually have a smaller Excel number than a someone from a younger generation. And this is coming from one who just bought his first pair of reading glasses. 🙂
After I create the first conditional format with red cells, I select the same cells and repeat the setup for the formula new rule for yellow colored cells. This will format cells yellow if the training date occurred 11 months ago.
This uses the logical AND() function which looks at 2 conditions to check if the date is at the 11 month ago mark. The AND() function looks to see if all conditions are true. Specifically, it checks to see if the date is between 364 and 334 days ago. So it will turn yellow to help recognize a date that is close to reaching the 1 year mark.
The final step is to select the date cells then click the create new rule for conditional formatting with a formula.
The Rules Manager is used to make edits to the conditional formatting. So if your rules gets messed up you would need to click Conditional Formatting –> Rules Manager.
Note: When you are writing the formula you do not want to use your mouse to click on the first cell of dates because it would make B2 absolute referenced to $B$2. If that happens just delete the $ signs. You only need to have a relative reference and Excel will automatically apply the formula to all cells selected. The “apply to” section should have the absolute reference since that is the range that is not being copied anywhere. The format painter is also a nice choice is you want to copy the conditional formatted cells from one worksheet to another. Even works from one workbook to another workbook. So feel free to download my sample file spreadsheet that has all these rules in it.
If you like this post and be sure to sign up for the mailing list so you will receive an email for each of my new post. And be sure to check out my YouTube video on Conditional Formatting examples.
New Post update video that uses the DATEDIF function which I believe is more elegant. https://stevechasedocs.wordpress.com/2016/02/15/using-the-datedif-function-in-excel-for-conditonal-formatting-expired-training-dates-video/
On a parting note, be sure to stop by my wife’s blog to see what’s for dinner tonight.