+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting Based on Date Above

  1. #1
    Registered User
    Join Date
    04-05-2016
    Location
    Orlando, FL
    MS-Off Ver
    2010
    Posts
    2

    Conditional Formatting Based on Date Above

    I have a table where the first column is a list of dates. The dates are in order, but some dates are skipped, and sometimes the date is repeated several times. My goal is to create conditional formatting so that a date cell is highlighted ONLY IF the date directly above it is at least 2 days before it (e.g. if the date in cell A5 is 3/11, it won't be highlighted if the date in cell A4 is either 3/10 or 3/11, but it will be highlighted if the date in cell A4 is anything before 3/10). New dates are added to the table all the time, so i would need a function that conditionally formats every new cell as it is entered. Is this possible?
    Last edited by slippy47; 03-08-2019 at 03:09 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Conditional Formatting Based on Date Above

    first question since joining in 2016! welcome. you can convert your range to a Table first. select your range (say A1:Z100) and press CTRL + T. this makes the range dynamic and will highlight after new data is added. once done, select the range you want to apply to (say from A2:A100).
    go to Home tab -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
    =$A2-$A1>=2

    if it's possible for blank cells, you might need to use:
    =AND($A2-$A1>=2,$A1<>"")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    04-05-2016
    Location
    Orlando, FL
    MS-Off Ver
    2010
    Posts
    2

    Re: Conditional Formatting Based on Date Above

    Oh thanks! Yeah I didn't realize that making absolute references would let the function dynamically change from row to row.

+ 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 for Gant Chart based on : Start Date / End Date / Status
    By Stancur in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-23-2018, 04:13 AM
  2. Conditional Formatting Based on Date
    By ashdougx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-15-2017, 04:56 PM
  3. [SOLVED] conditional formatting based on date
    By jackf-nc in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-21-2016, 03:07 PM
  4. Replies: 3
    Last Post: 11-23-2013, 06:21 AM
  5. Conditional Formatting based on Date
    By davidknowles in forum Excel General
    Replies: 2
    Last Post: 03-07-2012, 10:50 AM
  6. Conditional Formatting Based on Date
    By krie2509 in forum Excel General
    Replies: 3
    Last Post: 02-23-2010, 11:09 AM
  7. Conditional Formatting Based on Date
    By John F.M. in forum Excel General
    Replies: 1
    Last Post: 06-02-2005, 04:05 PM

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