Ask Your Question
0

Conditional formatting RAG status for upcoming and past dates

asked 2018-02-26 15:33:46 +0200

updated 2018-02-26 17:04:18 +0200

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)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-02-26 17:32:04 +0200

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())< p="">

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)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-26 15:33:46 +0200

Seen: 195 times

Last updated: Feb 26 '18