Conditional Formatting Based on Dates

 

Question asked by user @ Excel forum :-

Conditional Formatting based on dates

I have been searching but i cant solve my issue, I want to conditional format a cell that will be either, Green, Yellow or red depending on the date.

  • If the date I enter in Cell C2 is more than 60 days from today the cell should be green.
  • If the date is less than 60 days from today the cell should be yellow.
  • If the cell is less than 30 days from today the cell should be red

This is for drivers licence dates, i want to be alerted when they are 60 days from the expiration etc

 

image 1

 

To put the “Conditional Formatting” follow below given steps:-

  • Select the range, where we want to do “Conditional Formatting”.
  • When we will click on “Conditional Formatting” drop down list will appear, where we have to select “Manage Rules”  dialog box will get open.
  • Where we have to click on “New Rule”, New Formatting Rule dialog box will get appear, click on Use a formula to determine which cells to format.
  • First we will apply the “Conditional Formatting” for (If the date I enter in Cell C2 is more than 60 days from Today).
  • Enter the formula =AND(C2<>””, C2>TODAY()+60).
  • Click on Format and select the green color.

image 2

 

  • Again we will click on “New Rule”, New Formatting Rule dialog box will get appear, click on Use a formula to determine which cells to format.
  • Now will enter the formula for (If the date is less than 60 days from today).
  • Enter the formula =AND(C2<>””, C2<TODAY()-60)
  • Click on Format and select the Yellow color.

 

  • Again we will click on “New Rule”, New Formatting Rule dialog box will get appear, click on Use a formula to determine which cells to format.
  • In last we will enter the formula for (If the cell is less than 30 days from today).
  • Enter the formula =AND(C2<>””, C2>TODAY()-30)
  • Click on Format and select the Red color.

 

  • Click on Apply and then on ok.

 

image 5

 

 

Main Page

Conditional Format Based on Dates

Find Occurrence of Text in a Column

How to Highlight a row on the basis of Cell

Compare 2 Columns and Return Fill Red if is different

How to check the row and then highlight the first cell of the row

Highlight Cells Tomorrow Excluding Weekend

Conditional Formatting to Mark Dates on a Calendar

How to apply Conditional Formatting  in a Cell before a Particular Character

Highlight the Top 10 Sales through Conditional Formatting

Conditional Formatting for Pivot Tables

Conditional Format Between First and Last Non-Blank Cells

PDF

Excel



18 thoughts on “Conditional Formatting Based on Dates

  1. Surely this is all wrong, shouldn’t it be:

    =AND(C2””, C2>TODAY()+60)
    =AND(C2””, C2<=TODAY()+60)
    =AND(C2””, C2<=TODAY()+30)

    And set last one to highest priority.

    • Hi Krish,

      Thanks for taking time to look at the tricks.

      It would be nice if you can review your formula and can tell us what are you trying to say here. Also, it would be great if you read the original question posted carefully. Also, the formula which you are trying to use, will never return to TRUE as both condition will never meet. Can you check once and let us know.

      The Original condition is:
      I have been searching but i cant solve my issue, I want to conditional format a cell that will be either, Green, Yellow or red depending on the date.

      If the date I enter in Cell C2 is more than 60 days from today the cell should be green.
      If the date is less than 60 days from today the cell should be yellow.
      If the cell is less than 30 days from today the cell should be red

      • I tried the formula here and it doesn’t work for me.

        The way I read the question is if, assume today is 16/06/2015. Then tomorrow is red, 16/07/2015 is yellow, 16/08/2015 is green. Is this not the idea?

        In your example, if you assume your “today” was 15/04/2015, then you had 1 date in the future and 2 are in the past? I’m a little confused as to why you would do this.

        My formulas work for what I’m trying to achieve from the original question, but yours do not. Obviously, I forgot to add in “” in each of the formulas.

    • Hi Krish,

      The beauty of excel is to get the result via multiple ways. Here, AND function is taking care of value should not be equal to Blank.

      If you have another way to get this output then I am looking forward to it.

      I just checked your formula & found there is error. Please check.

      Regards,
      Ashish

      • As answered above I am answering the question in a different way, as I feel the dates used in the example aren’t relevant to what the question originally asked.

        Indeed I did mess up with the formulas:

        =AND(C2””, C2>TODAY()+60)
        =AND(C2””, C2<=TODAY()+60)
        =AND(C2””, C2<=TODAY()+30)

        Is what I should've posted.

        • Hmm whenever I type in the “not equal to” arrows they seem to disappear from my comments. Apologies as the formula is wrong again, because of this.

          • Hi Krish,

            we have updated the sheet, please let us know if it works fine now at your end.

            Thanks,
            Team Excel Forum & Excel Tip

    • Hi Latha,

      Thanks for taking time in reading our post.

      We haven’t used IF because the result easily can be done with “AND” function. If we use “IF” condition, then also we have to use AND along with IF because there are two conditions which needs to be TRUE. Once both condition will be TRUE, then only IF will show the result else nothing.

      But if you have any alternative where we can skip AND function and could be done without multiple condition, will highly appreciated.

      Thanks,
      Team Excel Forum & Excel Tip

  2. I too am having difficulty getting this formula to work despite applying today’s dates and adjusting the others as follows:
    Date Days Exceed Expiration Date
    16/06/2015 62 17/08/2015
    16/06/2015 -122 14/02/2015
    16/06/2015 -30 17/05/2015

    • Hello,

      There were some prob with range in the conditional formatting due to which the formula was not working. We have updated the sheet as well as the order.

      Team Excel Forum & Excel Tip

  3. I believe Krish to be correct about there being errors in your formula. If C2 is less than today minus 30 then any date over a month in the past would be yellow.

    Likewise if C2 is greater than today minus 60 then any date after 60 days ago will be red and also into the future including today and into the future

    • Hello Daniel,

      Yes, we have found an error and the same we had already revised on the page.

      Thanks for bringing this in our knowledge.

      Thanks,
      Team Excel Tip & Excel Forum

      • Hi Admin,

        I have reviewed the formula and still believe it to be incorrect, based on the idea of the question.

        In the formula you are using “Today – No.”, which means you are looking into the past. Whereas I believe the question is asking us to look into the future (ie 60 days from today), therefore we should be using “Today + No.” or “C2 – No.”.

  4. What about…

    Home/ Styles/ Conditional Formatting/ Highlight Cells Rules/ Greater Than…
    Format cells that are GREATER THAN: ==> =TODAY()+60
    with ==> Custom Format… (select desired green format)

    Home/ Styles/ Conditional Formatting/ Highlight Cells Rules/ Between…
    Format cells that are BETWEEN: ==> =TODAY()+30
    and ==> =TODAY()+60
    with ==> Custom Format… (select desired yellow format)

    Home/ Styles/ Conditional Formatting/ Highlight Cells Rules/ Between…
    Format cells that are BETWEEN: ==> 1/1/1910
    and ==> =TODAY()+30
    with ==> Custom Format… (select desired red format)

    Don’t forget the “=” before “TODAY()”.
    I hope this is helpful.

  5. the mentioned formula does not work because the result for:

    C2″” returns a #”NAME” error

    this formula works for me

    =AND(NOT(ISBLANK(C2)),C2< TODAY()-60)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>