Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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.

Conditional formatting based on EDATE() with multiple rules

share|improve this answer
That didn't quite work for me until I changed the , for a ; so my working formula/condition looks like this: EDATE(TODAY(); -3) Thanks. – andygrunt Aug 15 '11 at 12:20
Sorry, that's right - for OpenOffice in Windows, you need the semicolon to separate the arguments. Seperating by comma works under linux only. – tohuwawohu Aug 15 '11 at 12:27
Another amendment: the separator between the formula's arguments doesn't depend on the OS but on the language settings. With german lcalization, for example, you will need to use the semicolon ; as separator. The default english localization uses the comma ,. – tohuwawohu Aug 21 '11 at 9:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .