+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting (Colors) in a "Nested If" Situation

  1. #1
    Registered User
    Join Date
    07-18-2011
    Location
    Ruidoso NM
    MS-Off Ver
    Excel 2010
    Posts
    62

    Conditional Formatting (Colors) in a "Nested If" Situation

    Haven't figured out a way to search for this particular question, and scrolling through thousands of conditional formatting posts would be a tad tedious ....

    Column of cells next to a column of dates. Want the former to be either red, yellow, or green depending on whether the adjacent date is within one year, within the second year, or after the second year. Red is easy enough, ThatDate-Today<=365. The other two are tricky, as there can be anywhere from 1 to 365 days remaining in "Year 1," so how to refer numerically to Year 2 and Year 3 is eluding me. And then there's the whole "nested if" thing, which I understand in general but am not sure how to apply in a formula-driven conditional formatting situation. I could probably write a small, custom VBA function to do what I want, but surely it's possible to do this within the native Excel formulas and features ... ?

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,844

    Re: Conditional Formatting (Colors) in a "Nested If" Situation

    you would setup three conditional formats

    thatdate > today-365
    thatdate > today -730
    thatdate > today - 1095
    (would be an issue with leapyears)

    and put a stop if true on the conditional format rules

    if still an issue , i can post an example
    we could also use date() function

    date(year(cell with date) -1 , month(1) , day(1))
    date(year(cell with date) -2 , month(1) , day(1))
    date(year(cell with date) -3 , month(1) , day(1))
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    07-18-2011
    Location
    Ruidoso NM
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Conditional Formatting (Colors) in a "Nested If" Situation

    Ah, that stop if true is the key -- didn't know about that. Thanks!

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,844

    Re: Conditional Formatting (Colors) in a "Nested If" Situation

    Yep, quite useful, as it saves writing some extra code

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using "AND," "OR," and "TODAY()" in conditional formatting
    By KArnoldColumbia in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-18-2013, 01:17 PM
  2. [SOLVED] Conditional formatting - formula if cell does NOT contain "," or "@"
    By Armitage2k in forum Excel General
    Replies: 12
    Last Post: 12-02-2012, 06:23 AM
  3. Conditional Formatting - Replace date with "YES" or "NO"
    By christinie in forum Excel General
    Replies: 3
    Last Post: 11-01-2012, 04:10 PM
  4. Replies: 2
    Last Post: 08-17-2012, 05:10 AM
  5. excel should "paste special" a "conditional formatting"
    By lozturk21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-23-2005, 10:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1