How to Apply Conditional Formatting Based on Date in Another Cell in Excel

Conditional formatting is a feature in Excel that allows you to change the appearance of cells based on certain criteria. You can use conditional formatting to highlight cells that contain a date greater than, less than, or equal to a date in another cell. This can be useful for visualizing deadlines, due dates, expiration dates, etc.

To apply conditional formatting based on date in another cell, you need to use a formula that compares the date values in the cells. The formula should return TRUE or FALSE depending on whether the condition is met or not. For example, if you want to highlight cells that contain a date after a specific date in cell A1, you can use the formula:

=B2>$A$1

This formula compares the date in cell B2 with the date in cell A1. The $ signs make the reference to cell A1 absolute, meaning that it will not change when copied to other cells. The formula returns TRUE if the date in B2 is greater than the date in A1, and FALSE otherwise.

Procedures

To apply conditional formatting based on date in another cell, follow these steps:

1. Select the range of cells that you want to format. For example, select B2:B11.
2. Go to the Home tab and click on Conditional Formatting > New Rule.
3. In the New Formatting Rule window, select “Use a formula to determine which cells to format” as the rule type.
4. Enter the formula that compares the date values in the cells. For example, enter =B2>$A$1 to highlight cells that contain a date after the date in A1.
5. Click on the Format button and choose the formatting options that you want to apply. For example, choose a yellow fill color and a bold font style.
6. Click OK to close the Format Cells window, and then click OK again to close the New Formatting Rule window.
7. You should see the cells that meet the condition highlighted with the chosen format.

Example

Suppose you have a list of tasks and their due dates in column A and B, and you want to highlight the tasks that are overdue based on the current date in cell D1. You can use the following formula to apply conditional formatting:

=B2<$D$1

This formula compares the due date in cell B2 with the current date in cell D1. The formula returns TRUE if the due date is less than the current date, and FALSE otherwise. Here is how the table looks like after applying the conditional formatting with a red fill color and a strikethrough font style:

The tasks that are overdue are highlighted in red and crossed out.

Other Approaches

There are other ways to apply conditional formatting based on date in another cell in Excel. For example, you can use the built-in date rules that are available in the Conditional Formatting menu. These rules allow you to highlight cells that contain dates that are:

• Today
• Yesterday
• Tomorrow
• In the last 7 days
• Last week
• This week
• Next week
• Last month
• This month
• Next month
• Before
• After
• Between

To use these rules, follow these steps:

1. Select the range of cells that you want to format. For example, select B2:B11.
2. Go to the Home tab and click on Conditional Formatting > Highlight Cells Rules > Date.
3. Choose the date rule that you want to apply. For example, choose “Before” to highlight cells that contain a date before a certain date.
4. Enter the date that you want to compare with, or select a cell that contains the date. For example, enter 2024-01-16 or select cell D1.
5. Choose the formatting options that you want to apply. For example, choose a light red fill color and a dark red text color.
6. Click OK to apply the rule.

You can also create your own custom date rules using the “A Date Occurring” option in the Conditional Formatting menu. This option allows you to highlight cells that contain dates that are:

• In January
• In February
• In March
• In April
• In May
• In June
• In July
• In August
• In September
• In October
• In November
• In December
• In Q1
• In Q2
• In Q3
• In Q4
• In the first half of the year
• In the second half of the year
• In the first quarter of the year
• In the second quarter of the year
• In the third quarter of the year
• In the fourth quarter of the year

To use this option, follow these steps:

1. Select the range of cells that you want to format. For example, select B2:B11.
2. Go to the Home tab and click on Conditional Formatting > New Rule.
3. In the New Formatting Rule window, select “Format only cells that contain” as the rule type.
4. In the Edit the Rule Description section, select “Dates Occurring” from the first drop-down list, and choose the date option that you want to apply from the second drop-down list. For example, choose “In January” to highlight cells that contain a date in January.
5. Click on the Format button and choose the formatting options that you want to apply. For example, choose a green fill color and a white text color.
6. Click OK to close the Format Cells window, and then click OK again to close the New Formatting Rule window.
7. You should see the cells that meet the condition highlighted with the chosen format.