+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting with IF-THEN formula using DATES

Hybrid View

  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,677

    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,435

    Re: Conditional Formatting with IF-THEN formula using DATES

    Green
    Formula: copy to clipboard
    	=E9>A1-90	

    Yellow
    Formula: copy to clipboard
    =AND(E9<=A1-30;E9>=A1-90)

    Pink
    Formula: copy to clipboard
    =AND(E9<=A1;E9>=A1-30)

    Red
    Formula: copy to clipboard
    =E9>=A1		

+ 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. Conditional formatting with dates formula problem.
    By russell.estes@gmail.com in forum Excel General
    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