Excel 2007 – “Dates occurring” buggy?

Conditional formatting can come in handy to give the user a visual hint at the status of things. For example, if you have a list of invoice due dates, you could use conditional formatting to highlight invoices due in the previous month with red, leave invoices due this month in white, and apply a nice, cool green shade to dates that are in the following month, indicating that you don’t have to worry about payment just yet.

With Excel 2007, there is the pre-defined rule you can choose for conditional formatting.  Highlight your cells, click Conditional Formatting in the Home Ribbon, select the second option Format only cells that contain, then choose Dates occurring and select Last month, and finally select a red fill for the cell. Rinse and repeat for Next month, but with a green fill.

Sounds simple enough.

snapshot in December
snapshot in December

But when the year gets older and you hit December, you will suddenly realise, that the January dates are no longer lighting up! Apparently, Excel 2007 does not recognise January as a date one month later than December and the cell stays uncoloured.

Likewise, once you move into January, the December dates will no longer show in red! December is not recognised by Excel 2007 as being one month before January.

This is clearly a shortfall of Excel 2007, because in Excel 2010 the formatting behaves as expected.

Now, what to do until you can upgrade to Excel 2010? You can use a conditional format with the option Use a formula to determine which cells to format and then set these two rules:

correct format
correct format

For last month, colored with a red fill:

=MONTH(C2)=IF(MONTH(TODAY())=1,13,MONTH(TODAY()))-1

For next month, colored with a green fill:

=MONTH(C2)=IF(MONTH(TODAY())=12,0,MONTH(TODAY()))+1

If you want to compare two dates stored in cells, you can use a similar formula, like in this screenshot below where the dates in column A are compared with the dates in column C.

Conditionally formatting previous and following month
Conditionally formatting previous and following month

To color the previous month red:

=MONTH(A11)=IF(MONTH(C11)=12,0,MONTH(C11))+1

To color the following month green:
=MONTH(A11)=IF(MONTH(C11)=1,13,MONTH(C11))-1

The attached file has all the screenshots as examples. Of course, you gotta be quick to experience the bug, since it only shows in December and January, unless you manually reset your computer date!

DatesOccurring-bug.xlsx
first published: 27 December 2009