Conditional formatting RAG status for upcoming and past dates

[andrewj720]

I’d like to add conditional formatting to some cells which contain dates.

For dates more than 1 week in the future, the formatting should be a green cell background
For dates between today and 1 week in the future, the formatting should be an orange cell background
For all dates in the past, the formatting should be a red cell background.

Also, for compatibility with Excel 2016, should I save these files as .ods or .xlsx?

I can’t wok out how to do this - can anyone suggest a solution please?

Many thanks

(edit - I found this website that suggests formulae which do similar to what I want, but how/where do I enter these into Calc? i.e. do I select ‘Formula’ in the conditional formatting manager, but drop the cell name? I tried several permutations, but can’t get it working)

[andrewj720]

OK - solved it.

Open conditional formatting manager (from Format menu)

Add new condition.

Change ‘Cell Value is’ to ‘Formula is’

In field next to ‘Formula is’ type: =(H11<TODAY())

In apply style, add your preferred style (I made a red background)

Click Add, to add a second condition

Change ‘Cell Value is’ to ‘Formula is’

In field next to ‘Formula is’ type: =AND(H11>=TODAY(),H11<=(TODAY()+7))

In apply style, add your preferred style (I made an amber background)

Click Add, to add a third condition

Change ‘Cell Value is’ to ‘Formula is’

In field next to ‘Formula is’ type: =AND(H11>TODAY()+7)

In apply style, add your preferred style (I made a green background)