+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting - Expiration Dates (2 Years)

  1. #1
    Registered User
    Join Date
    07-30-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Conditional Formatting - Expiration Dates (2 Years)

    Hello All,

    Conditional formatting is relatively new to me. I have a spreadsheet that list various certification dates for my employees. Some of those certifications expire annually, and some expire every two years. I need all the dates to turn yellow 60 days prior to their expiration, and red upon expiration. I have received some help and have the annual dates already formatted, but I'm having problems with the 2 year expiration. Please note that the dates in question are the certification dates, not the expiration dates. For example:

    7/30/10 - I would like this to turn yellow on 5/30/12 and turn red on 7/30/12

    Any help would be appreciated!! Thanks!
    Last edited by viper6; 07-30-2012 at 02:37 PM. Reason: Solved

  2. #2
    Forum Contributor
    Join Date
    05-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    186

    Re: Conditional Formatting - Expiration Dates (2 Years)

    Can you upload a sample workbook to work with?


    or look here http://www.excelforum.com/excel-gene...ngle-cell.html I posted about a similar conditional formatting but with a differnt issue. If you take the "" out of the conditoinal formating it all works and does what you are lookign for.

  3. #3
    Registered User
    Join Date
    07-30-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Conditional Formatting - Expiration Dates (2 Years)

    When you say upload a sample (which I can do) do you mean just cut and paste? or is there an attachment method I'm missing here?

  4. #4
    Forum Contributor CheshireCat's Avatar
    Join Date
    10-11-2011
    Location
    Victoria, Canada
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    258

    Re: Conditional Formatting - Expiration Dates (2 Years)

    Assuming your date is in cell A2, then in the conditional formatting dialog box, select "Formula is" and use this for condition 1 expired (Red):


    Please Login or Register  to view this content.
    and this for condition 2 warning (Amber):

    Please Login or Register  to view this content.
    Docendo discimus.

  5. #5
    Registered User
    Join Date
    07-30-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Conditional Formatting - Expiration Dates (2 Years)

    @ CheshireCat - Seems to be a problem with th thread loading, but your adive appears to be just what I was loking for...can't believe it was that easy. I think I tried every variation bu that one!! Thanks for the help.

  6. #6
    Registered User
    Join Date
    03-26-2020
    Location
    New Zealand
    MS-Off Ver
    10
    Posts
    1

    Re: Conditional Formatting - Expiration Dates (2 Years)

    Hi. I have been looking for a way of doing this exact same thing, I followed all the steps and it works for ecah individual cell, but when I try to select more than one cell it doesnt seem to work.
    Help please

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting - Expiration Dates (2 Years)

    Quote Originally Posted by chikivim View Post
    Hi. I have been looking for a way of doing this exact same thing, I followed all the steps and it works for ecah individual cell, but when I try to select more than one cell it doesnt seem to work.
    Help please
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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