Sometimes, you may want to highlight cells that contain dates between two specific dates, such as holidays, deadlines, or events. This can help you visualize and analyze your data more easily. In this article, I will show you how to use conditional formatting and formulas to highlight dates between two dates in Excel.
Conditional formatting is a feature in Excel that allows you to apply different formats to cells based on certain criteria or conditions. For example, you can change the background color, font color, or font style of cells that meet a specific condition.
To highlight dates between two dates, we need to use a formula that returns TRUE or FALSE for each cell, depending on whether the date in the cell falls between the two given dates. The formula should use the AND function and the DATE function.
The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. For example, =AND(A1>10,A1<20) returns TRUE if the value in cell A1 is greater than 10 and less than 20, and FALSE otherwise.
The DATE function creates a valid date from individual year, month, and day components. For example, =DATE(2024,2,5) returns the date February 5, 2024.
Procedures
To highlight dates between two dates with conditional formatting, follow these steps:
- Select the range of cells that contain the dates you want to highlight. In this example, I will use the range B4:G11.
- On the Home tab, click the Conditional Formatting button, and then click New Rule.
- In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
- In the Format values where this formula is true box, enter the formula that checks if the date in the cell is between the two given dates. For example, if you want to highlight dates between August 1, 2024 and November 1, 2024, you can use this formula:
=AND(B4>=DATE(2024,8,1),B4<=DATE(2024,11,1))
Note: Make sure the cell reference in the formula is relative, so that it can adjust to each cell in the range. Also, you can use other cells or named ranges to store the start and end dates, instead of hard-coding them in the formula.
- Click the Format button, and choose the format you want to apply to the highlighted cells. For example, you can change the fill color, font color, or font style. Click OK to close the Format Cells dialog box.
- Click OK to close the New Formatting Rule dialog box. You should see the cells that contain dates between the two given dates are highlighted with the format you chose.
Example
To illustrate how this works, let’s create a scenario where we have a list of tasks and their due dates, and we want to highlight the tasks that are due between September 1, 2024 and October 31, 2024. Here is the sample data:
| Task | Due Date |
|---|---|
| A | 8/15/2024 |
| B | 9/10/2024 |
| C | 10/5/2024 |
| D | 11/15/2024 |
| E | 12/1/2024 |
To highlight the tasks that are due between September 1, 2024 and October 31, 2024, we can use the following formula in conditional formatting:
=AND(B2>=DATE(2024,9,1),B2<=DATE(2024,10,31))
Note: We use B2 as the cell reference, because it is the first cell in the range B2:B6 that we want to apply the conditional formatting to.
After applying the formula and choosing a yellow fill color, we get the following result:
| Task | Due Date |
|---|---|
| A | 8/15/2024 |
| B | 9/10/2024 |
| C | 10/5/2024 |
| D | 11/15/2024 |
| E | 12/1/2024 |
As you can see, only the tasks B and C are highlighted, because their due dates are between September 1, 2024 and October 31, 2024.
Other Approaches
There are other ways to highlight dates between two dates in Excel, such as using the DATEDIF function, the DAYS function, or the NETWORKDAYS function. However, these functions may have some limitations or drawbacks, such as:
- The DATEDIF function is not documented in Excel, and may return inaccurate results for some arguments.
- The DAYS function only returns the number of days between two dates, and does not account for leap years or other calendar variations.
- The NETWORKDAYS function only returns the number of working days between two dates, and does not include weekends or holidays.
Therefore, using the AND and DATE functions is a simple and reliable way to highlight dates between two dates in Excel.
