Table of Contents
Conditional formatting is a feature in Excel that allows you to format a cell based on the value in another cell. To apply conditional formatting, you must first select the cell or range of cells to be formatted, then go to the Home tab and select ‘Conditional Formatting’ from the ribbon. From there, you can select the desired condition, such as ‘Greater Than’, and enter the cell reference of the cell you want to compare against. If the value in the cell you have selected meets the condition, the conditional formatting will be applied.
To apply conditional formatting to cells in Excel based on the date in another cell, 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 Based on Date in Another Cell
Suppose we have the following dataset in Excel that shows the date that various people applied for some position:
Suppose the cutoff date for the application was 10/15/2022 and we would like to apply conditional formatting to any date that is after this cutoff date.
First, we can type this date in cell E1:
Next, we can highlight the cells in the range B2: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 =B2>$E$1 in the box, then click the Format button and choose a fill color to use.
Once we press OK, all of the cells in the range B2:B11 that have a date after 10/15/2022 will be highlighted:
If you change the cutoff date in cell E1, then the conditional formatting rule will automatically update.
For example, suppose we change the cutoff date to 1/5/2023.
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.