Excel - Conditional Formatting using Dates

December 2016

Microsoft office software contains an Excel application that has an excellent tool called Conditional formatting: this allows you to apply formats to a cell or set of cells. You can highlight due or overdue dates using this feature by applying a formula. To conditionally format dates in MS Excel, MS office software introduced many short cuts. Click conditional formatting from the menu by selecting some cells, choose Highlight cells rules and select a date. You can format the dates quickly in your data using this feature meeting certain criteria. In MS Excel you can also choose colors to make your spreadsheet easier to read. Have a look at the article below to get some concrete examples on how to apply conditional formatting rules.


Issue

I am trying to change the color of an entire row to 3 different states depending on the result.

The row needs to stay clear if the date in a particular cell (say B2) is after TODAY


OR

I want the row to change to say, orange, if the date in a particular cell (say B2) is Today but only if another cell (say C2) is blank.
If the other cell (C2) isn't blank then I want it to stay clear.


OR

The row needs to go red if the date in a particular cell (say B2) is before TODAY but only if another cell (say C2) is blank.
If the other cell (C2) isn't blank then I want it to stay clear.

I think that makes sense: in effect, I want to highlight items that are due today, overdue and leave clear if they not due yet.

Solution

This is how it can be achieved in Excel 2007.
  • Highlight a row that you wish to format (or highlight all the data in that row only)
  • Select 'conditional formatting' from the ribbon menu
  • Manage rules
  • New rule:


For the first one you need to select the cell with the date 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 in any color you like. This means if the date in cell b2 is later than today it will format the line with the color selected.

=$B$2<TODAY()
Then format the color required. This means if the date is before today's date then it will format the row with the color selected.

=$B$2 = TODAY()
then, again format the color desired. This means that if the date in cell b2 is today's date then it will format it to the color selected.

Then create the next rule as follows:

=$A$2=""
then select the clear/white for the color. This means if cell a2 is empty then color of the row will remain clear/white. But you must also tick the stop if true box at the end of the rule.

This means that if cell, a2, is blank then the rest of the rules are ignored.

Hope you can understand/decipher that.

Note that

Thanks to sharpman for this tip on the forum.

Related :

This document entitled « Excel - Conditional Formatting 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.