Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Conditional formatting w/color

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Japan
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I maintain a large list of people and track training dates. My problem is that I need a way to conditionally format cells so that when these dates turn a year old, they highlight.
    ex. Supervisor Safety - Completed 12Jan01
    I need this date highlighted since it is more than a year old......

    PLEASE HELP!!!! This is driving me nutz!

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let's say your list starts in cell A2; follow these steps:

    (1) Select A2.
    (2) Click on Format > Conditional Formatting.
    (3) Under the script Condition 1, click the down arrow and select "Formula Is".
    (4) In the field to the right, enter (or paste in) this formula:
    =A2-DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))<=0
    (5) Click the Format... button, and choose your formatting preferences.
    (6) Click OK, then OK.
    (7) Copy A2 and paste special for Formats as far down as you need to.

    Tom Urtis

  3. #3
    Guest

    Default

    =Now()-A2>365.25 should also work.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That it will, plus it's *just a few* characters shorter than mine.

    Thanks.

    T.U.

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anon or Tom

    I've tried using this with 11 months but no luck. I reduced 365.25 to 335.25 and get a "false" response.

    Brian

  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Guys,

    I got it. Using the wrong <>!!!

    Brian

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Japan
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You guys are great!!! If you ever come to Okinawa, the beer is on me!

    Got one last question... what is the .25 on the end of 365 for?

    Thanks
    Rob

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A year doesn't have exactly 365 days, that's why every 4 years we have a Feb 29th, it's just a way of adding the 1/4 of a day that each year has.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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