### 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** - 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.

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