+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting: Cell highlights after a given date

  1. #1
    Registered User
    Join Date
    12-09-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Conditional formatting: Cell highlights after a given date

    Hello,

    I have a multiple sheet workbook. I would like to highlight given cells with the value of 0 when the current date reaches a given date. IE: On the December worksheet I want a cell to highlight after December 15th if it contains 0., I have a worksheet for each month, So I will be using this formatting multiple times (with a different date for each month).

    I have dug and dug, and I can't nail this one down. I have tried using =today() and Date functions, and I cannot get it right.

    Any help, O masters of Excel?

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,607

    Re: Conditional formatting: Cell highlights after a given date

    And ( TODAY()>DATEVALUE("15/12/13"), Cell=0)

    i have added A1=""

    assuming the range starts in A1

    for 2007 or 2010 excel version
    Conditional Formatting

    Highlight applicable range >>

    the range you want to apply the cond format to


    Home Tab >> Styles >> Conditional Formatting
    New Rule >> Use a formula to determine which cells to format
    Edit the Rule Description: Format values where this formula is true:


    =AND( TODAY()>DATEVALUE("15/12/13"), A1=0,A1<>"")

    cell would be the first in the range


    Format… [Number, Font, Border, Fill]
    OK >> OK

    should work

    my example has
    =AND( TODAY()>DATEVALUE("8/12/13"), A1=0,A1<>"")
    to test
    Attached Files Attached Files
    Last edited by etaf; 12-09-2013 at 04:45 PM.
    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
    12-09-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Conditional formatting: Cell highlights after a given date

    This did not work. I feel I'm close though. I am a little confused on the AND function. Also, does the date format have to be day/month/year? Can it be reversed? Should the whole year be input instead of just the last 2 digits ("2013" vs "13")

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Conditional formatting: Cell highlights after a given date

    I would suggest that you put both TODAY() and the date you wish to reference against, in their own cells, that way you can reference them in the CF rule, and the format needs to match your regional format...mm/dd/yy - yy or yyyy doesnt matter.

    etaf is from UK, date format there is dd/mm/yy
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,607

    Re: Conditional formatting: Cell highlights after a given date

    add an example spreadsheet ?

  6. #6
    Registered User
    Join Date
    12-09-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Conditional formatting: Cell highlights after a given date

    I have got it! Thanks. I went with Ford's suggestion of assigning dates to cells. Thanks for your help

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Conditional formatting: Cell highlights after a given date

    Happy to help and thanks for the feedback
    Last edited by FDibbins; 12-09-2013 at 07:12 PM.

+ 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. [SOLVED] Conditional Formatting that highlights cells if data has changed
    By jonathan.haynes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-09-2013, 05:07 AM
  2. [SOLVED] Conditional Formatting that highlights cells on two conditions
    By jonathan.haynes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-07-2013, 04:54 AM
  3. Replies: 6
    Last Post: 05-31-2013, 04:49 AM
  4. Conditional Formatting Value highlights multiple values
    By brent_bris in forum Excel General
    Replies: 15
    Last Post: 04-22-2013, 11:26 AM
  5. Conditional Formatting Help-highlights a single row
    By Tommy1005 in forum Excel General
    Replies: 1
    Last Post: 02-22-2011, 02:22 PM

Tags for this Thread

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