+ Reply to Thread
Results 1 to 6 of 6

Need to highlight a cell based on a one year expiration

  1. #1
    Registered User
    Join Date
    03-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Need to highlight a cell based on a one year expiration

    I have a very basic spreadsheet with names and dates of last training. The training is due every year so if they took it on 1/1/2013 it would be due again on 1/1/2014.

    In A3 to A7, I have names. In B3 to B7 I have their last training date. I want the date cells to highlight yellow when they are within 60 days of expiring and then highlight red when they are more than 1 year old.

    I have tried several different things and cannot make it work. I am very much a novice so as simple as possible would be great...

    TIA
    Stephen

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Need to highlight a cell based on a one year expiration

    use a formula and two rules

    =b3<=date(year(today())-1,month(today()),day(today()))
    format red

    next rule
    =b3<=date(year(today()),month(today()),day(today())-305)
    format yellow
    that will test 60 days before year end

    make sure you order - red first , then yellow and use stop if true

    and apply to the range
    $B$3:$B$7

    if you post your spreadsheet we can add for you
    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
    03-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need to highlight a cell based on a one year expiration

    Worked perfectly... Thx...

    How about something to apply to a cell (with the same restrictions as above) but the cell is blank? With the formatting I applied, the cell turns red if it is empty with no data in it.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Need to highlight a cell based on a one year expiration

    you could add a new rule and put first

    =B3=""
    no format

    OR add to the rules you have

    =AND(B3<=date(year(today())-1,month(today()),day(today())), B3<>"")
    format red

    next rule
    =AND (B3<=date(year(today()),month(today()),day(today())-305), B3<>"")
    format yellow

    if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

  5. #5
    Registered User
    Join Date
    03-11-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need to highlight a cell based on a one year expiration

    Absolutely perfect.. Thank you so much... Reputation added

    I sincerely appreciate it...

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Need to highlight a cell based on a one year expiration

    your welcome, thanks for the rep

+ 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] Beginning of Year Numbers Based on Expiration
    By sulax in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-29-2013, 11:11 AM
  2. Replies: 2
    Last Post: 12-18-2012, 11:18 AM
  3. Replies: 2
    Last Post: 06-11-2012, 06:08 PM
  4. Year expiration date
    By kwriram in forum Excel General
    Replies: 3
    Last Post: 10-29-2008, 11:36 AM
  5. [SOLVED] how can I highlight a cell if it meets year and month criteria
    By Clyde in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2006, 09:30 AM

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