Free Online Microsoft Training

Free tips and tricks for using Microsoft Office and Windows

Free Online Microsoft Training

Free tips and tricks for using Microsoft Office and Windows

Use conditional formatting to identify dates within a specific range

Print Friendly, PDF & Email

My conditional formatting to identify dates earlier than today post has proven to be extremely popular. This post talks about how to use conditional formatting for dates which occur in the past. I have had lots of questions and comments based on this post. A popular question is how to use conditional formatting to highlight a date range. That range might be based on today’s date but includes the previous 14 days. It may be from today and for the next 14 days.

If you haven’t used conditional formatting in Excel before, be sure to check out my post outlining how to use conditional formatting in Excel.

Create a reference cell for dates

In this conditional formatting exercise, we are going to reference a cell within the worksheet which will be used within the formatting rule. By referencing a cell, we give ourselves more flexibility on being able to change what is being highlighted by the conditional formatting.

  1. Open the workbook you wish to apply the formatting to.
  2. We need to designate some cells within the worksheet that we will use to enter our date range(s).
  3. In my example, I have put the label “Date” into cell E1.
  4. In cell F1 I will use a formula to calculate the date range I want to use.
  5. First up we are going to keeping this simple. We will just have Excel identify any date which is before today.
  6. In cell F1 enter the formula =TODAY():
  1. Continue on to the next section to set up the conditional formatting rules. Later on, we will look at other date combinations we can use in cell F1 and the surrounding cells.

Apply the conditional formatting

  1. Highlight the column which contains the dates you wish to work with.
  2. From the Home tab select the Conditional Formatting button and choose New Rule.
  3. The New Formatting Rule dialog box will appear:
  1. From the Select a Rule Type, choose Format only cells that contain.
  2. In the Edit the Rule Description section you need to tell Excel that any cell which contains a date which is less than today’s date:
  1. In the Format only cells with section, leave the first drop down menu set as Cell Value.
  2. For the next drop down menu change it to less than as shown below:
  1. In the formula box, we need to reference the cell in our worksheet which has the date stored.
  2. Click the arrow to collapse the New Formatting Rule dialog box:
  1. Use the mouse to select cell F1 from your worksheet.
  2. You will see the cell reference entered as =$F$1 (an absolute cell reference).
  3. This conditional formatting rule will look for a cell value that is less than the value we have in cell F1:
  1. Now to specify a format you wish to apply, click the Format button.
  2. I have chosen to have the text change to Red and Bold:
  1. Click OK.
  2. Click OK again to complete the Conditional Formatting rule.
  3. Any dates which occur prior to today’s date will now appear in the formatting you selected:

Date Range Options

Now that we have some basic conditional formatting displayed on our worksheet, which is highlighting days which are before today. Let’s look at how we would vary the date range to suit our needs.

Scenario 1: Highlight dates which fall within two dates E.g. within the last fortnight (14 days)

  1. Leave cell F1 as it is, it should contain the =TODAY() formula.
  2. In cell G1 let’s calculate what the date was 14 days prior to today.
  3. Enter the following formula =F1-14 (today’s date minus 14 days):
  1. Because we have used today’s date as our start date, this formula will continue to change the date range as each day passes because it will always calculate from today. If you want to look at a set date range, rather than it changing each day, remove the formula from cell F1 and change it to the end of the date range you want to reference. E.g. to look at 30 April back 14 days, make cell F1 30/04/2024 and then cell G1 will automatically calculate back 14 days.
  2. Now highlight the column of dates which have the conditional formatting applied.
  3. Select Conditional Formatting from the Home tab and choose Manage Rules.
  4. Select your formatting rule and click Edit Rule.
  5. Change the rule option from “less than” to “between” which will allow us to reference two dates.
  6. The reference to F1 will remain, now link the second field to G1 using the same method as above:
  1. Click OK twice.
  2. Your formatting should adjust accordingly.

Scenario 2: Highlight dates which occur in the future E.g. occur in the next fortnight

  1. In cell F1 it should contain the =TODAY() formula.
  2. In cell G1 let’s calculate what the date will be in 14 days from to today.
  3. Enter the following formula =F1+14 (today’s date plus 14 days):
  1. You will not need to make any changes to the Conditional Formatting rule as we have already made the change in the previous scenario.
  2. Your formatting should adjust accordingly and highlight any date which falls between today and the next 14 days.

You have now used conditional formatting to highlight a date range either in the past or in the future. Love this article? Comment below and let me know how you’ve utilised this feature. Be sure to check out my other Microsoft Excel content here.


5 Responses

  1. Hello. If I had a list of completion dates for people that took an online course, and I wanted to colour the dates that were overdue the frequency that the course should be completed, can I achieve this? As the course should be completed every 2 years, and each person completes the course when they join, I need to add a rule so that when 2 years from each persons completion date is passed, it colours red.

  2. Hi,

    I want to change a row’s colour based on the one cells vale(Date)(First Cell in the first cell of the row) so that all rows that are less than today’s date, turn blue, keep today’s dates white and change future dates green.

  3. Thanks for this.

    I have a question.

    I’d like to highlight dates that fall between today’s date and lets say 14 days in the future (so the above works perfectly).

    But what if I want to add an additional condition? (for example it has to fall between those dates AND contain the Letter A (lets say, from another column).

    Hopefully that makes sense.

    1. Hi Aaron,

      In this case you couldn’t use conditional formatting for the entire process. You could however use an IF statement to identify which records meet both your criteria and then have the IF statement return a text result. You could then use conditional formatting to them colour rows based on that text result.
      Hope this helps.
      Thanks, Belinda

Leave a Reply

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

14 + five =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

.pf-button-img { padding-top: 20px; padding-bottom: 20px; }