Conditional Formatting Based On Another Cell Date in Excel

To highlight the time difference and the estimated time you can use conditional formatting based on another cell date. To get an insight into problems like delivering orders within time or completing projects within time etc. you can use conditional formatting. In this article, I’m going to explain how to use Excel Conditional Formatting based on another cell date.

Let’s have a quick look at the formulas we are going to use for conditional formatting and relevant outputs from them.

Download Practice Workbook

3 Ways to Use Conditional Formatting Based On Another Cell Date in Excel

To clarify this explanation, I will use a sample dataset of an online fruit shop. I’ve taken 4 columns that represent the order date and the delivery date of a particular order. These columns are Product Name, Amount, Order Date, and Delivery Date. The Date format is MM/DD/YY.

Let’s checkout the following three ways for using conditional formatting based on another cell date in Excel.

1. Using AND Function Comparing Multiple Dates

You can use the AND function to compare multiple dates using Conditional Formatting based on different cell dates.

To demonstrate the procedure, I’ve considered the delivery date within the same day and the next day as the estimated time. I’ll highlight the dates that match the criteria.

To start the procedure,
First, select the cell or cell range to Highlight a value based on another cell date.
➤ I selected the cell range D4:E10
Now, open the Home tab >> from Conditional Formatting >> select New Rule

➤ A dialog box will pop up.
From Select a Rule Type select Use a formula to determine which cells to format.

➤ In Format values where this formula is true type the following formula.

Here, the AND function will check whether the cell E5 is greater than equal H5 then it will also check E5 is less than equal H6. If both conditions are fulfilled, then it will Highlight the cell date.

Now, click on Format to select the format of your choice to Highlight the cell.

Again, a dialog box will pop up. Select any color to Highlight your cell.
➤ I selected the Dijon color to fill.

Then, click OK.

In the end, click OK.
Therefore, It will Highlight the cell values of the Order Date and Delivery Date columns where the date is equal to or less than the estimated date of 3/13/2021.

Read more: Excel Conditional Formatting Based on Date in Another Cell

Similar Readings:

• How to Use Conditional Formatting in Excel Based on Dates
• Excel Conditional Formatting Dates
• Excel Conditional Formatting Based on Date Range
• How to Do Conditional Formatting Highlight Row Based On Date

2. Using Comparative Opertaor

You can use the Less Than Equal (<=) operator to find out on-time deliveries within the estimated time within Conditional Formatting.

To start the procedure,
First, select the cell or cell range to Highlight a value based on another cell date.
➤ I selected the cell range E4:E10
Then, open the Home tab >> from Conditional Formatting >> select New Rule

➤ A dialog box will pop up.
From Select a Rule Type select Use a formula to determine which cells to format.

➤ In Format values where this formula is true type the following formula.

➤ From Format select the format of your choice to Highlight the cell date.
To choose the Format follow the explained steps from Section-1.

In the end, click OK.
Thus, It will Highlight the cell values of the Delivery Date column where the date is less than or equal to the estimated date of 3/13/2021.

3. Using DATEVALUE Function in Conditional Formatting

You can use the DATEVALUE function while using the Conditional Formatting based on another cell date.

In this section, I’m going to determine whether the delivery dates exceeded the expected time limit or not and highlight which are within the estimated time.

Let’s start the procedure,
First, select the cell or cell range to Highlight the on-time deliveries based on another cell date.
➤ I selected the cell range B4:E10
Next, open the Home tab >> from Conditional Formatting >> select New Rule

➤ A dialog box will pop up.
From Select a Rule Type select Use a formula to determine which cells to format.

➤ In Format values where this formula is true type the following formula.

Here, the DATEVALUE function will check whether cell E5 is less than equal to the given date of 3/13/2021. If this condition is fulfilled, then it will Highlight the selected cell range.

➤ From Format select the format of your choice to Highlight the cell date.
To choose the Format follow the explained steps from Section-1.

Finally, click OK.
As a result, It will Highlight the cell values of the Product Name, Amount, Order Date, and Delivery Date columns depending on the condition less than or equal to the estimated date of 3/13/2021.

Conclusion

In this article, I’ve explained 3 methods of Excel Conditional Formatting based on another cell date. Depending on your problem or need you can follow any of the different ways to highlight a cell based on another cell date. For any kind of suggestions, ideas, and feedback please feel free to comment down below.

Further Readings

• Apply Conditional Formatting to the Overdue Dates in Excel (3 Ways)
• 4 Quick Excel Formula to Change Cell Color Based on Date
• Excel Conditional Formatting for Dates within 30 Days (3 Examples)
• Excel Conditional Formatting Dates Older than Today (3 Simple Ways)