Use conditional formatting to identify dates within a specific range
Modified on: Fri, 5 Nov, 2021 at 2:24 PM
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.
Open the workbook you wish to apply the formatting to
We need to designate some cells within the worksheet that we will use to enter our date range(s)
In my example, I have put the label “Date” into cell E1
In cell F1 I will use a formula to calculate the date range I want to use
First up we are going to keep this simple. We will just have Excel identify any date which is before today
In cell F1 enter the formula =TODAY()
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
Highlight the column which contains the dates you wish to work with
From the Home, tab select the Conditional Formatting button and choose New Rule
The New Formatting Rule dialog box will appear:
From the Select a Rule Type, choose Format only cells that contain
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.
In the Format only cells with section, leave the first drop down menu set as Cell Value
For the next drop down menu change it to less than as shown below:
In the formula box, we need to reference the cell in our worksheet which has the data stored
Click the arrow to collapse the New Formatting Rule dialog box
Use the mouse to select cell F1 from your worksheet
You will see the cell reference entered as =$F$1 (an absolute cell reference)
This conditional formatting rule will look for a cell value that is less than the value we have in cell F1
Now to specify a format you wish to apply, click the Format button
I have chosen to have the text change to Red and Bold
Click OK again to complete the Conditional Formatting rule
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)
Leave cell F1 as it is, it should contain the =TODAY() formula
In cell G1 let’s calculate what the date was 14 days prior to today
Enter the following formula =F1-14 (today’s date minus 14 days)
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.
Now highlight the column of dates which have the conditional format applied
Select Conditional Formatting from the Home tab and choose Manage Rules
Select your formatting rule and click Edit Rule
Change the rule option from “less than” to “between” which will allow us to reference two dates
The reference to F1 will remain, now link the second field to G1 using the same method as above
Click OK twice
Your formatting should adjust accordingly
Scenario 2: Highlight dates which occur in the future E.g. occur in the next fortnight
In cell F1 it should contain the =TODAY() formula
In cell G1 let’s calculate what the date will be in 14 days from today
Enter the following formula =F1+14 (today’s date plus 14 days)
You will not need to make any changes to the Conditional Formatting rule as we have already made the change in the previous scenario
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.