+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting with IF-THEN formula using DATES

  1. #1
    Registered User
    Join Date
    03-02-2015
    Location
    Seattle,WA
    MS-Off Ver
    MSOffice 2010
    Posts
    2

    Conditional Formatting with IF-THEN formula using DATES

    Good Morning.
    I am working with certificate expiry dates and want to have conditional formatting to highlight as the expiry dates get closer and/or is past due.
    Here is what my need is:
    If (DATE)E9 is equal to A1(Current date) or greater, then turn the cell Red
    IF (DATE)E9 is less than (DATE)A1-30(DAYS), but greater than (DATE)A1 then turn the cell Pink
    IF (DATE)E9 is less than (DATE)A1 minus 90(DAYS) AND greater than (DATE)A1 minus 30(DAYS) then turn the cell Yellow
    If (DATE)E9 is greater than (DATE)A1 minus 90(DAYS) then turn the cell Green.
    Thanks for the assistance!

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Conditional Formatting with IF-THEN formula using DATES

    Quote Originally Posted by GottaQ View Post
    Good Morning.
    IF (DATE)E9 is less than (DATE)A1-30(DAYS), but greater than (DATE)A1 then turn the cell Pink
    ...
    Something wrong with this logic:

    If A1=30-Apr

    E9<31-Mar and E9>30-Apr

    what E9 should be?
    Quang PT

  3. #3
    Registered User
    Join Date
    03-02-2015
    Location
    Seattle,WA
    MS-Off Ver
    MSOffice 2010
    Posts
    2

    Re: Conditional Formatting with IF-THEN formula using DATES

    bebo021999, thanks here is my correction from your query (lines 2 and3).
    IF (DATE)E9 is equal to A1(Current date) or greater, then turn the cell Red
    IF (DATE)E9 is between (DATE)A1(DAYS) and (DATE)A1 minus 30(DAYS) then turn the cell Pink
    IF (DATE)E9 is between (DATE)A1 minus 90(DAYS) and (DATE)A1 minus 30(DAYS) then turn the cell Yellow
    IF (DATE)E9 is greater than (DATE)A1 minus 90(DAYS) then turn the cell Green

    Does this give you clearer logic I am trying to create?

  4. #4
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,294

    Re: Conditional Formatting with IF-THEN formula using DATES

    Green
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Yellow
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Pink
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Red
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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 or Formula for Repeating Dates
    By long8484 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2014, 02:28 PM
  2. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  3. [SOLVED] formula for conditional formatting (count of dates)
    By joeldlow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2014, 05:04 AM
  4. Formula or conditional formatting for dates
    By Phillipengela in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2013, 02:11 PM
  5. Replies: 8
    Last Post: 03-05-2005, 08:06 PM

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