Have you ever wondered how to automatically highlight expired dates and dates that are less than 30 days from expiration? An option in excel called “conditional formatting” holds the key to this task. If you are an avid excel user tracking calibration dates, evaluation dates, birthdays, shipping, expenses or a number of other date related content, then you will definitely benefit from this tip.
In this article we are going to walk through the steps to have excel automatically change the color of a cell or the color of a font after a certain date has passed. Consequently, every time you open your spreadsheet it’s automatically updated with the appropriate color changes.
For example, you are monitoring calibration dates and want to know at a glance (by color code) what equipment is past due for calibration and which is coming due. The following steps will walk you through making these changes.
First open a new blank excel sheet to work from. Put in cell A1 the word “Equipment” and in cell B1 put “Due Date.” Now populate cells A2 through A5 with various equipment names and populate cells B2 through B5 with various dates. For this example it’s best to put in varying dates from last year, some from the current week and some from the months or years ahead.
Select the Home tab in the toolbar at the top of the screen if you are not already there. When you open excel it should default to this screen. Now select the cells that you want to apply the formula to such as the cells in column B. You can select the entire column by clicking on the B in that column. From the top tool-bar in excel click on Conditional Formatting and choose New Rule from the drop down menu. When the next dialog box opens click on the option “Format only cells that contain”. This will change the options in the dialog box. On the bottom half of the dialog box, click “Cell Value” from the first (far left) drop down menu. Select “Less than” from the second drop down menu then put your cursor in the text box to right of that and enter the following formula without the quotes “=NOW( )+30”.
Next we will set the formatting we want to apply by clicking on the “Format” button in the bottom right corner of the dialog box. This one may be familiar to you if you have ever changed the color of a cell or text in excel and we will be doing the same thing here. Now select the color that you want the cell to shade to for items that will expire in the next 30 days by clicking on the “Fill” tab at the top of the dialog box and selecting the color from the color chart. (Note: if you don’t want to change the color of the box but rather you want to change the color of the font only then click on the “Font” tab and select your color from the color chart.)
When your color is selected, click the “OK” button and this will take you back to the “New Formatting Rule” dialog box. Now click on the Okay button here and it should take you back to the “Conditional Formatting Rules Manager” dialog box. If for some reason it does not then simply click on the Conditional Formatting option from the tool-bar at the top of the excel sheet and choose “Manage Rules” from the drop down menu. Here you will notice that the rule you just created is listed in the dialog box. The next couple of steps will seem like deja vu but what we are doing is simply creating 3 similar rules but with different formulas to produce a single action in excel.
From the “Manage Rules” dialog box click on “new rule.” From the top part of the dialog box again select “format on cells that contain” option and in the lower part of the dialog box select “cell value” in the first drop down menu and “less than” from the second dialog box. Now place your cursor in the formula text box and enter the following code, again without the quotation marks. “=NOW( )”
Next step is to click on the “format” button to change the color of the cell or font for this rule. If you want to change the color of the cell then click on the “fill” tab for changing the color of the font, click on the “font” tab and select the color of your choice. For this exercise we will select red. Click OK to save changes and this should return you to the new rule manager, click OK here as well.
Now we are back to the first level rule manager and you should see your two new rules listed here. Again, click on the “new rule” button and from the top part of the dialog box we are going to select the option “use a formula to determine which cells to format” as the rule type. In the formula bar that appears type in the following without the quotes, “=ISBLANK(B1)=TRUE”. Next click on the “format” button to select what actions will apply when the conditions of this rule are met. If you are changing the color of the cell then click on the “fill” tab, if you are changing the color of the font then click on the “font” tab. For the purpose of this example we are going to choose white for the fill color and “automatic” for the font color, depending on which you are doing. Click the OK button and then click the next OK button on the rule dialog box and you should be taken back to the rules manager dialog box.
There should be 3 rules listed now in order by creation with the newest on top. You should have 2 “cell value” rules and one “formula” rule. On the far right of each rule you will see a “stop if true” check box. Click on the check box for the “formula” rule but leave the other 2 rules unchecked. Now click the OK button to close the dialog box. Now, click the “Apply” button, then click the “OK” button.
Congratulations, you have just entered lines of code in excel to create a rule of conditions. Save the excel sheet as “test1” in a folder of your choice or on your desktop and close it. The next time you open the document the color rules will take effect. If you would like to play around with this try changing the date on your computer to another day in the past or future and re-open the excel sheet. Notice the cells change colors based on the date change.
With this new knowledge at hand you can make the changes to your existing documents by applying this process to the dates you want to monitor.
Also visit my new “About me” blog at https://www.aubreywlove.com
Pail Writer 