Excel - Conditional Formatting or IF using Dates

September 2016

Excel is a very handy tool for working with data. It has many built-in features like conditional formatting which help in formatting and highlighting the data based on certain rules. The rules can be based on number values or ranges, text values or using dates as well. For formatting using dates, one has to select the conditional formatting ribbon. Then one has to create a new rule using date criteria and the relevant colors to highlight different rows. All these options make Excel a great tool for data handling.


Issue

You're trying to change the color of an entire row to 3 different states depending on the result.
  • The row needs to stay clear when the date in a particular cell (let's say B2) is after TODAY.
  • OR
  • I want the row to change to orange when the date in a particular cell (let's say B2) is Today but only if another cell (let's say C2) is blank.
  • If the other cell (still C2) isn't blank then I want it to stay clear.
  • OR
  • The row needs to change to red when the date in a particular cell (let's say B2) is before TODAY but only if another cell (let's say C2) is blank.
  • If the other cell (still C2) isn't blank then I want it to stay clear.

Solution

Highlight a row you wish to format (or highlight all data on that row only)
  • Select 'conditional formatting' from the ribbon menu
  • Manage rules
  • New rule
  • For the first one you will need to select the cell that the date is in that you wish to action
  • In my case I am using A2 and B2
  • Create the following rules (x3 in any order)
  • =$B$2>TODAY() Then format this any color you like. This means when the date in cell B2 is later than today, format the line with the color selected.
  • =$B$2<TODAY() Then format the color required. This means when the date is before today's date, then format the row with the color selected.
  • =$B$2 = TODAY() Then, again format the color desired. This means that when the date in cell B2 is today's date the color selected color
  • Then create the next rule as follows:
  • =$A$2="" Then select the clear/white for the color. This means that if cell A2 is empty then color the row clear/white. You must tick the stop if true box at the end of rule.
  • What this will do is if cell A2 is blank, then the rest of the rules are ignored.

Note that

Thanks to sharpman for this tip on the forum.

Related :

This document entitled « Excel - Conditional Formatting or IF using Dates » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.