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 conditional format dates within occur in the past. I have had plenty 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()
Add the TODAY formula
  1. Continue on an set up the conditional formatting rules and later on, we will look at other options we can use in cell F1.

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, highlight it.
Conditional Format 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:
Conditional Format Date
  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
Click the arrow to collapse the window
  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
These are the Conditional formatting rule settings
  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
Conditional Format Dates
  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:
Conditional format to highlight a date range

Date Range Options

Now that we have a basic conditional format 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)
Add the second date into cell G1
  1. Because we have used today’s date as our start date, this formula will continue to change the date ranges as each day passes because it will always calculate the from today. If you want to look at a set date range, rather than it changes each day, remove the formulas 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/2019 and then cell G1 will automatically calculate back 14 days.
  2. Now highlight the column of dates which have the conditional format 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
Conditional format to highlight a date range
  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)
Enter the formula to calculate the date in 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.

Facebook
Twitter
LinkedIn
Pinterest

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 *

15 − 10 =

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

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