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 keep 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 and 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.
  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 data 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 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)
  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
  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 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.

Reference: https://www.thetraininglady.com/conditional-formatting-dates-range/