+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Conditional Formating using dates

Hybrid View

  1. #1
    Registered User
    Join Date
    09-19-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    3

    Conditional Formating using dates

    In need of some help with conditional formating using dates. I am working on completing a workbook that will use a green, yellow, red color format to highlight dates that are less than 9 month old, 10-12 months old, and greater than 12 months old, respectively. Any suggestions? Thanks.

    Chris

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Conditional Formating using dates

    Chris,

    Attached is an example workbook based on the sample criteria provided. In column A is a list of dates. Column A has been conditionally formatted with the following:

    Rule 1 (>12 months old = Red):
    =AND($A1<>"",(12*(YEAR(TODAY())-YEAR($A1))+(MONTH(TODAY())-MONTH($A1)))>12)

    Rule 2 (10-12 months old = Yellow):
    =AND($A1<>"",(12*(YEAR(TODAY())-YEAR($A1))+(MONTH(TODAY())-MONTH($A1)))>9)

    Rule 3 (<=9 months old = Green):
    =AND($A1<>"",(12*(YEAR(TODAY())-YEAR($A1))+(MONTH(TODAY())-MONTH($A1)))<=9)

    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-02-2011
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Conditional Formating using dates

    These rules that tigeravatar put in are exactly what I wanted to do on my spreadsheet but I cannot find where I can put in the multiple rules. In 2003 I knew where to find it. In 2007 I cannot find where to put the multiple rules. PLEASE tell me how to get to the location that I can input these rules.

    Thanks in advance
    Tigger3406

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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