+ Reply to Thread
Results 1 to 5 of 5

Conditional format based on dates

  1. #1
    Registered User
    Join Date
    03-17-2011
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    25

    Conditional format based on dates

    I have been searching but i cant solve my issue,
    I want to conditional format a cell that will be either, Green, Yellow or red depending on the date
    If the date i enter in cell G9 is more than 60 days from today the cell should be green
    if the date is less than 60 days from today the cell should be yellow
    if the cell is less than 30 days from today the cell should be red
    This is for drivers licence dates, i want to be alerted when they are 60 days from the expiration etc

    E.G todays date 4/10/15
    I have a date 6/10/15 the cell should be yellow
    I have a date 5/10/15 the cell should be red
    I hope im making sense here!

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

    Re: Conditional format based on dates

    use three rules in conditional formatting

    PUT in this order and stop if true
    Green
    =AND(G9<>"", G9<TODAY()-60)

    Yellow
    =AND(G9<>"", G9<TODAY()-30)

    RED
    =AND(G9<>"", G9>=TODAY()-30)

    see attached example sheet


    for 2007, 2010 or 2013 excel version
    Conditional Formatting

    Highlight applicable range >>

    G9


    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(G9<>"", G9<TODAY()-60)


    Format… [Number, Font, Border, Fill]
    choose the format you would like to apply when the condition is true
    OK >> OK

    Repeat with a new rule for the Yellow and Red formula
    Attached Files Attached Files
    Last edited by etaf; 04-10-2015 at 12:55 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
    03-17-2011
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Conditional format based on dates

    Thank you!

  4. #4
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Conditional format based on dates

    Hi User - If your query has been resolved, please take few seconds to mark this thread as SOLVED.



    Cheers!!!

    Anil Dhawan
    Thanks,
    Anil Dhawan


    Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.

    Don't stop when you are tired. STOP when you are done!

  5. #5
    Registered User
    Join Date
    10-29-2012
    Location
    Leicester, England
    MS-Off Ver
    Office 2010
    Posts
    9

    Re: Conditional format based on dates

    Sorry etaf but must disagree.

    I read the question as looking for dates in the future not in the past.

    So:
    Less than 30 days in the future = red
    Less than 60 days but more than 30 in the future = yellow
    More than 60 days in the future = green

    I believe the correct formatting is:
    GREEN
    =AND(G9<>"", G9>TODAY()+60)
    YELLOW
    =AND(G9<>"", G9>TODAY()+30)
    RED
    =AND(G9<>"", G9<=TODAY()+30)

    Please check my logic and correct me if you find any errors.

+ 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. Replies: 4
    Last Post: 09-19-2014, 08:10 AM
  2. [SOLVED] conditional format based on dates.
    By 11ktran44 in forum Excel General
    Replies: 3
    Last Post: 03-26-2014, 12:55 PM
  3. Conditional Format Cell based on Dates in Other Cells
    By kortekn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-21-2013, 11:50 AM
  4. Conditional format between dates based on today()
    By StevieNix in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-27-2012, 06:06 PM
  5. Conditional Format Dates in a Calender when Matches dates in a list
    By Lungfish in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2009, 06:23 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