+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting based on date - delete format if a date is entered in next cell

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Okotoks, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    5

    Conditional formatting based on date - delete format if a date is entered in next cell

    So, I know how to conditional format a cell based on whether the date entered is past today's date by 3 days.
    Using the 'USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT' =TODAY()-H15>3
    What I would like is for the conditional format not to apply once the actual date has been entered in the adjacent cell.
    Attached Files Attached Files

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

    Re: Conditional formatting based on date - delete format if a date is entered in next cell

    try
    =AND(I1="",TODAY()-H1>3)
    for your conditional format formula
    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
    08-30-2012
    Location
    Okotoks, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional formatting based on date - delete format if a date is entered in next cell

    Hi etaf, thanks for the reply, that has moved my Conditional Format on but I still aren't getting what I need to complete the sheet.
    If the date entered in H15 is more than 3 days away from the current date, then I need the cell to be green, if the date entered is within 3 days of today I need it to turn orange and finally, if the date entered is today or past today I need the cell to turn red. The final thing would be for the cell not to be filled once a date is entered into I15.
    These are the formulas I used:
    =AND(I15="",H15-TODAY>3) for green.
    =AND(I15="",H15-TODAY()+3) for orange.
    =AND(I15="",H15-TODAY()) for red.

    It doesn't work though.

    Todays date is 22-11-13.
    I have also included a screen shot.KEVINPERSIMMON - WIN_20131122_095755.jpg

  4. #4
    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,686

    Re: Conditional formatting based on date - delete format if a date is entered in next cell

    you have the formula incorrect

    for green
    =AND(I1="",TODAY()-H1>3)

    for orange
    =AND(I1="",H1<TODAY()) - BUT this needs to come after the green format rule and stop if true must be used
    then if the date is greater than 3 days , it wont turn orange as the green rule has been applied

    for red
    =AND(I1="",H1=TODAY())
    Attached Files Attached Files

+ 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. Conditional format a cell based on a due date
    By Taiter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2013, 11:50 PM
  2. Conditional Date Formatting with a Custom Cell Format
    By kqueen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-28-2012, 01:48 PM
  3. Replies: 0
    Last Post: 05-25-2009, 06:58 PM
  4. Conditional Format based on date of another cell
    By marjo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-29-2008, 02:41 PM
  5. Delete rows based on date range entered?
    By fzrdiva in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-29-2006, 08:52 AM

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