+ Reply to Thread
Results 1 to 2 of 2

Conditional formatting - highlight between date range

  1. 09-04-2016, 08:00 PM #1
    Kiwi129
    Kiwi129 is offline
    Registered User
    Join Date
    09-04-2016
    Location
    Alberta, Canada
    MS-Off Ver
    2013
    Posts
    1

    Post Conditional formatting - highlight between date range

    Hi,

    Sorry if this topic has been answered before, I'm very new to excel and can't find the answer I need.

    In the attached image I want Column J (Passport Expiry) to flag red if it is less than 6 months the date in from Column E (Date of Departure)

    So essentially every time I add in a new row of information I would like excel to flag passports that are expiring less than 6 months of away from their individual departure dates.

    Could you help with formulas or how to add?

    Thanks in advance for your help

    Capture.PNG
    Reply With QuoteRegister To Reply
  2. 09-04-2016, 08:32 PM #2
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Conditional formatting - highlight between date range

    Hello,

    click the cell with the first passport expiry date. Let's assume that this is cell J4.

    Click Home ribbon > Conditional Formatting > New Rule > Use a formula to determine ... and then enter this formula into the formula box

    =EDATE($E4,6)>$J4

    If this is not for row 4, please adjust the row numbers accordingly.

    Select a format and click OK on all dialogs. Now you can copy cell J4 and use Paste Special > Formats to the other dates in column J.

    If you turn the data into an Excel Table object (Insert > Table), the conditional formatting will automatically be applied to new rows.

    cheers, teylyn
    Microsoft MVP
    http://www.teylyn.com
    Twitter: http://twitter.com/IngeborgNZ
    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 to Highlight a Range when Today is between Two Dates DD/MM
    By AliGW in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-04-2015, 11:10 AM
  2. [SOLVED] Conditional Formatting - highlight cells if range count = 0
    By VBA FTW in forum Excel General
    Replies: 2
    Last Post: 11-25-2013, 04:45 PM
  3. How to use conditional formatting to highlight date
    By SSS86 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2013, 03:47 PM
  4. Conditional Formatting to highlight duplicate numbers in a range
    By mungel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-21-2013, 02:54 PM
  5. [SOLVED] Conditional Formatting: Highlight a cell containing a date after 1 year past that date!
    By Christopher1990 in forum Excel General
    Replies: 5
    Last Post: 08-22-2012, 05:06 PM
  6. highlight comparison using conditional formatting with formula in range
    By tjc0ol in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-29-2011, 08:48 AM
  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