• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 755
  • Last Modified:

RAG conditional formatting based on relative date


in 2007 how can I do RAG congitional formatting based on the number of days from today? e.g. say the value of a cell is <3 days from now it should have a red background, 5-3 should be green and >5 is white?

  • 2
1 Solution
Rob HensonIT & Database AssistantCommented:
You can use the Formula option for CF using the TODAY() function. I could give an example in Excel 2003 format as working at a 2003 machine but not at a 2007 machine until this evening.

Rob H
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

it's not quite clear what you mean with "<3 days from now". Is that 3 days past or three days in the future?

If it is days in the future, you can set up rules with the following formulas:

=AND(TODAY()-A1>=3,TODAY()-A1<5) (green)
=TODAY()-A1<3 (red)

If you mean days in the past, adjust the formulas.

see attached.

cheers, teylyn
bowemcAuthor Commented:
how can i easily copy the formatting from one cell to another. I don't want to have to manually set-it all up. Shout the format paintbrush work reliably?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If you use relative cell referencing, as I have given above, you can just copy the cell and use Paste Special > Formats

Or, select the whole table when defining the conditional format and make sure the referencing applies to the current cell, i.e. don't use $A$1 but A1. That will format the whole table at once.

Or, set the conditional format for the one cell, using relative referencing. Then, use the Conditional Formatting > Manage Rules dialog to specify the range that the rule shall be applied to.

Many paths lead to Rome.

It depends on what you are comfortable with. If in doubt, post a sample file and I'll be happy to show what I mean, applied to your file.

cheers, teylyn

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now