+ Reply to Thread
Results 1 to 5 of 5

A conditional formatting rule that will highlight a cell based on certain criteria/date

  1. #1
    Registered User
    Join Date
    04-24-2014
    Location
    NH, USA
    MS-Off Ver
    Excel 2013
    Posts
    2

    A conditional formatting rule that will highlight a cell based on certain criteria/date

    I have a spreadheet that contains several different certifications per employee, based on the type of certification depends on when it will expire. For example, the CPR certification will expire two years from the date listed in the cell. I need to write a conditional formatting rule that will highlight the cell yellow when the date is a month away from expiration, and red when it is in the month of expiration. I need the rule to highlight based on soley the month ad not by the actual date in the month. If the date is 4/24/12, i need to highlight the cell yellow when todays date contains any day in March 2014 and red when todays date comtains any day in April 2014.

    Currently i have the following rules written but I noticed they highlight only when the expiration date hits for the exact day in the month and not the month as a whole.

    =EDATE(TODAY(),-23)>=I10
    =EDATE(TODAY(),-24>=I10

    Thank you

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,173

    Re: A conditional formatting rule that will highlight a cell based on certain criteria/dat

    try

    =AND( I10>=DATE(YEAR(TODAY()), MONTH(TODAY())-23, DAY(1)), I10<=DATE(YEAR(TODAY()), MONTH(TODAY())-22, DAY(1)-1) )

    =AND( I10>=DATE(YEAR(TODAY()), MONTH(TODAY())-24, DAY(1)), I10<=DATE(YEAR(TODAY()), MONTH(TODAY())-23, DAY(1)-1) )
    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
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: A conditional formatting rule that will highlight a cell based on certain criteria/dat

    May be this.......

    For Yellow........

    Please Login or Register  to view this content.
    and for Red......

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Registered User
    Join Date
    04-24-2014
    Location
    NH, USA
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: A conditional formatting rule that will highlight a cell based on certain criteria/dat

    Works great! Thanks for your help!

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,173

    Re: A conditional formatting rule that will highlight a cell based on certain criteria/dat

    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. Replies: 5
    Last Post: 07-03-2013, 12:51 AM
  2. Replies: 7
    Last Post: 06-10-2013, 09:39 AM
  3. Replies: 5
    Last Post: 04-22-2011, 02:50 PM
  4. Replies: 2
    Last Post: 02-21-2010, 05:53 PM
  5. Multiple Criteria per conditional formatting rule
    By Jonathan78 in forum Excel General
    Replies: 13
    Last Post: 09-18-2009, 05:50 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1