Conditional Formatting Based on Another Cell Date in Excel

Working with large data can be complex and we might have to conditionally format a large volume of data to get certain data from a cluster. In this article, we will be discussing briefly about how we can do conditional formatting in Excel based on another cell date as reference. This is primarily done by using the Conditional Formatting option in the Styles Menu of the Ribbon.

Contents hide

📁 Download Practice Workbook

Overview of Conditional Formatting in Excel

Learn to Do Conditional Formatting Based on Another Cell Date in Excel with These 3 Suitable Examples

1. Conditional Formatting Between Two Dates

2. Conditional Formatting Based on Dates in a Different Column

3. Conditional Formatting Based on Date in Specific Cell

Conditional Formatting Using TODAY Function in Excel

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📁 Download Practice Workbook

Download the practice workbook below.

Conditional Formatting Based on Another Cell Date.xlsx

Overview of Conditional Formatting in Excel

Conditional Formatting means to change the style or appearance of a cell that meets the criterion or condition specified. Conditional Formatting is a widely used option in Excel. We can use this option to filter and highlight certain wanted values based on conditions applied in the formula. We can demonstrate conditional formatting through the image below. Here, the cells highlighted specify a certain condition met by the range of cells selected.

Learn to Do Conditional Formatting Based on Another Cell Date in Excel with These 3 Suitable Examples

To demonstrate things easily, we will consider a dataset where different products and their delivery dates are present. We will now put different criteria to do conditional formatting of data based on another cell date. The dataset is shown as follows:

Example

1. Conditional Formatting Between Two Dates

In this approach, we will use conditional formatting to find or filter out data between two certain dates. The dates that belong within these two limits will be highlighted according to our given condition. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

At first, we pick a start date and an end date. Suppose the start date is 15th of October and the end date is 15th of November. We will highlight the Delivery dates within this range.
We select the cell range D6:D13.
Then we go under the Home tab and from the Styles section in the Ribbon, we select Conditional Formatting and then click on New Rule.

A popup box named New Formatting Rule will come. We select Rule Type as Use a formula to determine which cells to format option.
After that we write this formula below using AND function in Format values where this formula is true box and click on the Format option.

=AND($D6>=$F$6,$D6<=$G$6)

We go to Fill tab and select the yellow color in which we want to highlight our data and press OK.

We press OK again in New Formatting Rule popup box.

The resulting output highlighting the cells will look like the following image.

Example

2. Conditional Formatting Based on Dates in a Different Column

In this approach of conditional formatting, we will be comparing two columns that have two different dates such as Delivery Date and Received Date.Using the dataset, we will find out the products that haven’t been delivered at the same day, that is the products that were delivered late. The steps are as follows:

⬇️⬇️ STEPS ⬇️⬇️

At the beginning, we select the cell range D6:E13.
Then we go under the Home tab and from the Styles section in the Ribbon, we select Conditional Formatting. Then we click on New Rule.

A popup box with the name New Formatting Rule box will come out. We select Rule Type as Use a formula to determine which cells to format option.
After that we write this formula below in Format values where this formula is true box and click on the Format option.

=$D6<$E6

We go to Fill tab and select the yellow color in which we want to mark our data and press OK.

We press OK again in New Formatting Rule Box.

The results will look like the following image.

Example

3. Conditional Formatting Based on Date in Specific Cell

In this approach, we will be specifying a date in a cell and use that cell as reference in conditional formatting. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

Firstly, we will select the cell range D6:D13.
After that, we will go to Home tab and select New Rule from Conditional Formatting option.

A New Formatting Rule box will come out. We select Rule Type as Use a formula to determine which cells to format option.
After that we write this following formula in Format values where this formula is true box and click on the Format option.

=$D6>$F$6

We go to Fill tab and select the yellow color in which we want to filter our data and press OK.

We press OK again in New Formatting Rule Box.

The output image will look like the one below.

Conditional Formatting Using TODAY Function in Excel

Another important approach in which we can do conditional formatting of dates is by using TODAY Function. This function returns the present date and can be used to filter out data referencing from the present date. The steps for this method are as follows:

⬇️⬇️ STEPS ⬇️⬇️

In an adjacent cell, we mark today’s date just for the idea.
Then, we select the cell range D6:D13.
After that, we go to Home tab and select New Rule from Conditional Formatting option.

From New Formatting Rule box, we select Rule Type as Use a formula to determine which cells to format option.
Then we write down this formula.

=D6<TODAY()-60

Then we select the Format option. Next, we go to Fill tab and select yellow colour to highlight the formatted cells and press OK.

We press OK again and the output results will be filtered in yellow like the following image.

📄 Important Notes

🖊️ We must take note while using absolute cell references whether we’re doing absolute or mixed cell references. An error in this part might not give us the output we want.

🖊️ The shortcut key F4 can toggle us from absolute to mixed to relative cell references by each press.

🖊️ We should take care in maintaining a particular date format across the sheets.

📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌 We can do conditional formatting based on another cell date by writing different formulas in the Format values where this formula is true box by selecting the New Rule option in the Conditional Formatting section.

📌 Alternatively, we can use the TODAY Function to reference dates from today to apply a particular condition.

Conclusion

In conclusion, I hope that this article has provided you with insights about how we can do conditional formatting based on another cell date in Excel. If you want to know more about Excel, please visit our site ExcelDen and if you have any queries regarding this topic, feel free to let us know down below in the comment section.