How do I use conditional formatting in a spreadsheet based on dates?

Question

I have a spreadsheet in OpenOffice V3.3.0 (used in Windows 7 32 bit). One column contains dates (in the format: 14 August 2011 ) and I want to use conditional formatting on those cells depending on how far in the past it is.

For example, if the date is within 3 months (of today’s date), the cell background colour should be red. If it’s between 3 and 6 months, the background should be yellow and if the date is over 6 moths ago, the background should be green.

I can’t see how to set conditions based on dates – can anyone help?

Answer 1

Generally, you can do this by comparing the cell value with the result of the TODAY() function. There's a nice blog post about this.

In short:

• select cell value comparison ("Cell value is...") in the Conditional Formatting dialogue;
• select "less than" as operator;
• as comparison criteria, enter either TODAY() to catch all past dates, or TODAY() - 10 for dates 11+n days ago.

To compare based on months, you could use the EDATE function: EDATE(TODAY(), -3) results to the date three months ago. If the date in the current cell is more then three months ago, the conditional formatting rule will be true and the conditional formatting will apply.

To compare against multiple conditions, make sure to put the most special comparison clause to the top, since OpenOffice won't check further conditions if a rule is satisfied. This means: to apply different styles based on different dates in the past, put the oldest date at first position.