+ Reply to Thread
Results 1 to 4 of 4

Using conditional formatting to highlight cells based on date + 1 other condition

  1. 06-08-2017, 11:35 AM #1
    cciatti
    cciatti is offline
    Registered User
    Join Date
    03-11-2017
    Location
    Cleveland
    MS-Off Ver
    office 2016
    Posts
    3

    Using conditional formatting to highlight cells based on date + 1 other condition

    Hi people! I could use some help on a conditional formatting formula. I want to highlight a "status" cell if 2 conditions are met. One condition being the date in another column, the other condition being text that is showing in the column to be highlighted:

    Column Q is the "status" column that shows either "inbound" or "complete"

    Column P shows the date this item was shipped.

    My goal is to have cell in Column Q highlighted in RED if the date in Column P is more than 4 days ago from todays date AND if the cell in Column Q does not say "complete"

    The range of rows for these cells are 2-201.

    Basically I am looking to be alerted when an item I am having shipped hasn't been delivered within 4 days of the shipping date. Thank you for you help in advance!
    Reply With QuoteRegister To Reply
  2. 06-08-2017, 11:46 AM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Using conditional formatting to highlight cells based on date + 1 other condition

    Select the data to be highlighted

    Conditional Formatting
    New Rule
    Use a formula to determine...

    =AND(Q1 < > "Complete",P1 < TODAY()-4,P1 < > 0)

    Format as required
    Use Format painter (paintbrush icon) to copy to other cells
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 06-08-2017, 03:14 PM #3
    cciatti
    cciatti is offline
    Registered User
    Join Date
    03-11-2017
    Location
    Cleveland
    MS-Off Ver
    office 2016
    Posts
    3

    Re: Using conditional formatting to highlight cells based on date + 1 other condition

    pardon my ignorance...do I have to delete the spaces? That formula gives an error..
    Reply With QuoteRegister To Reply
  4. 06-08-2017, 07:31 PM #4
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Using conditional formatting to highlight cells based on date + 1 other condition

    You'll need to delete the spaces between < > to give <>.
    Also, since your range starts in row 2, change P1 and Q1 to P2 and Q2.
    In other words:
    1. Select Q2:Q201
    2. Apply this CF rule:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    3. Choose red fill.

    Edit: I would personally also change the last part to just >0 or to <>"" - but SpecialK's <> 0 will work fine (so the CF won't be applied if there isn't a date in column P).
    Last edited by Aardigspook; 06-08-2017 at 07:37 PM. Reason: Add mention of options for last part of formula
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 - Highlight row based on date in I column
    By mtpockets in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-22-2015, 03:26 PM
  2. [SOLVED] Help with Conditional formatting to highlight cells based on changing values
    By hmc907 in forum Excel General
    Replies: 4
    Last Post: 11-18-2014, 04:12 AM
  3. [SOLVED] A conditional formatting rule that will highlight a cell based on certain criteria/date
    By FEL2014 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-24-2014, 03:51 PM
  4. Using Conditional Formatting to highlight cells based on approaching dates
    By BrutalExcel in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 06-12-2013, 11:56 AM
  5. [SOLVED] Using Conditional Formatting to highlight cells based on days of the month
    By BrutalExcel in forum Excel General
    Replies: 2
    Last Post: 06-06-2013, 01:10 AM
  6. Excel 2007 : Using conditional formatting to highlight entire row based on date in one cell
    By vulturesrow in forum Excel General
    Replies: 5
    Last Post: 04-22-2011, 02:50 PM
  7. Excel 2007 : Highlight Row Based On Date, Conditional Formatting Already Used
    By grinch247 in forum Excel General
    Replies: 2
    Last Post: 02-21-2010, 05:53 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1