Example Formula for Highlighting Dates by Period in Excel

Sometimes, while analyzing data, you may need to highlight dates within a specific time period. It's even better if the dates are highlighted automatically based on a condition that updates whenever the start and end dates of the period are changed.



How to Highlight Dates by Color for a Specific Time Period in Excel

date selection

Below is an illustration of how you can use conditional formatting to automatically highlight a range of values containing dates between the start and end dates. If the start or end date changes, the conditional formatting will adapt and automatically highlight all the relevant cells based on the new condition.

highlight dates.

To create a rule for automatic color formatting based on a condition, follow these steps:

  1. Select the target range of cells that contains the dates you want to highlight (in this example, D2:D17). Then, choose the tool: "HOME" - "Conditional Formatting" - "Create Rule." A window titled "New Formatting Rule" will appear, as shown below:
    2. Create rule.
  2. In the window that appears, select the option "Use a formula to determine which cells to format." This allows you to use a formula with a logical expression to define the formatting conditions. If the formula returns TRUE for a particular cell, the formatting specified by the user will be applied to that cell.
  3. Enter the following formula into the input field:

    =AND(D2>=$A$2,D2<=$B$2)

    Note that the AND function is used to compare the date in the target cell D2 with the start and end dates, located in cells $A$2 and $B$2. If the date in the target cell falls between the start and end dates, the formula returns TRUE, and the specified format is applied to those cells. The formula uses absolute references for the start and end dates, while the reference to the cells being formatted is relative.
  4. Click the "Format" button to select the desired color and formatting style for the cells. The "Format Cells" window will appear, where you can customize the formatting. After setting your preferences, click OK on all open windows to apply the changes.
Click the button.

Dynamic highlighting of dates using interactive controls in Excel:

date selection

Download Example Formula for Highlighting Dates by Period in Excel download file

As a result, only the dates within the specified time period are highlighted in color from the list of dates.