Results 1 to 7 of 7

Thread: Conditional formatting of dates

  1. #1

    Conditional formatting of dates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there,

    I really hope that this is not a repeat of anything already answered a dozen times, but I've genuinely looked on-line for sometime and cannot seem to see a definitive answer to my query regarding colour conditional formatting of dates to reflect the following:

    1. If date in cell is less than today then text is RED.
    2. If date in cell is equal to or greater than today, but less than 5 days in future, then text is AMBER.
    3. If date in cell is equal to or greater than 5 days from today, then text is GREEN

    I can usually do one or two of the above, but never seem to get all 3 to work.

    Any help would be appreciated.

  2. #2

  3. #3
    Hi Bob,

    Thanks for the link. At first glance it looks perfect, but unfortunately it does not appear to be working on my Excel 2010 sheet.

    When I use the formulas mentioned, it seems to work for the dates that are less than today, but the remaining formulas either do nothing or change all entries.

    It also does not dynamically update the conditionally formatted cells if I were to input a date less than today. If I were to choose to change the colour fill for a cell, then it changes more cells than just where the date is less than today. For example, blank cells.

    I'll keep looking...thanks again though! )

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Check that the formulas you entered don't have quotes around them. That is a common error.


  5. #5
    No sign of any quotes...just rechecked.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Can you post a sample workbook showing this?


  7. #7
    Thanks for requesting example workbook for further review etc...I've cut down my requirements and can now use:

    Conditional Formatting / Highlight Cells Rules / Less than =Today()
    Conditional Formatting / Highlight Cells Rules / A Date Occurring "Next Week"

    This should be sufficient....

    Thanks again...this is a great site!

Posting Permissions

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