Table of Contents
If a cell contains a date that falls between two other dates, then it indicates the date is within the range of the two other dates. This can be used to filter out data that falls within a given time frame. For example, if you wanted to analyze data within a certain date range, you could use this logic to identify the data that falls within that range.
To apply conditional formatting to cells that are between two specific dates in Excel, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.
The following example shows how to use this option in practice.
Example: Apply Conditional Formatting if Cell is Between Two Dates
Suppose we have the following dataset in Excel that shows the number of sales made on various dates at some store:
Now suppose that we would like to apply conditional formatting to each row in the dataset that has a date between 1/15/2023 and 2/15/2023.
First, we can define these start and end dates in cells E1 and E2, respectively:
Next, highlight the cells in the range A2:B11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
In the new window that appears, click Use a formula to determine which cells to format, then type =AND($A2>=$E$1,$A2<=$E$2) in the box, then click the Format button and choose a fill color to use.
Once we press OK, all of the rows in the dataset that have a date between 1/15/2023 and 2/15/2023 will be highlighted:
If we change the start or end date to a different date, then the conditional formatting rule will automatically adjust to highlight the correct cells.
For example, suppose we change the end date to 3/15/2023 instead:
Now all rows with a date between 1/15/2023 and 3/15/2023 are highlighted.
Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.