• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • 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 HensonFinance AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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