Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Conditional Format based on date (2003)

    Lounger's

    I want to try to change a Conditional Format based on date - ie a date is in A1, B2 calculates A1 + 6 months - I would like to be able to change the color in B2 when the calculated date is exceeded.

    Not sure if this is possible.

    Any thoughts?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format based on date (2003)

    If I understand you correctly, you want to check whether the (calculated) date in B2 is later than today. If so:
    - Select B2.
    - Select Format | Conditional Formatting...
    - Select 'Cell Value' in the first dropdown (it is the default setting).
    - Select 'Greater than' in the second dropdown.
    - Enter the formula =TODAY() in the box next to it.
    - Click Format... and set the formatting you want.
    - OK your way out.

    If this is not what you want, please provide a more detailed explanation.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format based on date (2003)

    Well, Hans beat me to the punch and I like his solution better but here is a similar approach. As usual, there is more than one way to accomplish something in Excel.


    I am a little confused when you say "the calculated date is exceeded" exceeded by what? However, if you mean that today's date exceeds the calculated date then you could do the following:

    1) select the cells in Column B
    2) Go to FORMAT, Conditional formatting on the menu
    3) in the drop down box for condition 1. choose Formula.
    4) Enter the following formula in the space provided =today()>$B1
    5) click on the format button then the pattern tab and choose the color you want.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Conditional Format based on date (2003)

    Hans - Thanks very much - just right (as usual!)

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Conditional Format based on date (2003)

    Don - thanks for your help - yes you're right it when today exceeds the calculated date

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •