RAG conditional formatting based on relative date

Posted on 2012-08-22
Last Modified: 2012-08-22

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?

Question by:bowemc
    LVL 31

    Expert Comment

    by:Rob Henson
    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
    LVL 50

    Accepted Solution


    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

    Author Comment

    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?
    LVL 50

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now