Highlight dates between

We can apply conditional formatting to cells containing dates to highlight dates between two dates. Using the AND function in conditional formatting custom formula rule, each date in selected cells is evaluated against two given dates and all those cells are highlighted where dates fall between given dates. This article will step through the process.

Figure 1. How to Highlight Dates Between

Suppose we have a dataset of various tasks with their assigned and completion dates. We want to highlight those completion dates in column E that fall between 1/11/2019 and 1/25/2019, given as Date 1 and Date 2.

Figure 2. A dataset of Assigned Tasks

Steps to Highlight Dates Between

• Select the range of cells where completion dates are listed (E2: E11)
• Go to Home tab > Conditional Formatting > Select New Rule

Figure 3. Creating Conditional Formatting Rule

• In New Formatting Rule window, select rule type “Use a formula to determine which cell to format”.
• In the formula bar, enter the formula =AND($E2>=$H$2,$E2<=$I$2)

Figure 4. Creating a Custom Formula Rule

• Now, click on the Format button, and select the Cells Formatting style like Font, Border or Fill. For our example, select Fill > Red color and press OK

Figure 5. Selecting Cells Formatting Style

• To apply the custom formula rule and selected formatting style again press OK

Figure 6. Applying Conditional Formatting Rule

The AND formula evaluates each date in selected cells in E2: E11 against two dates given in cells H2 and I2. The formula returns TRUE where the dates in cells E2: E11 fall between the given dates and cells are conditionally highlighted with the selected formatting style.

Figure 7. The output of Conditional Formatting

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.