Excel Conditional Formatting Based On Another Cell Date (4 Ways)

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.

To make this explanation clearer, I’m going to 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. Here, I’ve taken the estimated delivery date as the same day or the next day of the order date.  The Date format is MM/DD/YY.

Sample Dataset

Download to Practice

4 Ways to Excel Conditional Formatting Based On Another Cell Date

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

Using AND Function in Conditional Formatting Based On Another Cell Date

➤ 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.

=AND($E4>=$H$4,$E4<=$H$5)
Here, the AND function will check whether the cell E4 is greater than equal H4 then it will also check E4 is less than equal H5. 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.

Using AND Function in Conditional Formatting Based On Another Cell Date

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.

Using AND Function in Conditional Formatting Based On Another Cell Date

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

2. Find Delay in Conditional Formatting Based On Another Cell Date

Let’s say, we want to find out the delayed deliveries, to do so we need to compare two dates again using the AND function.

Here, if the delivery date exceeds the estimated time (within the next day of order) then it will be considered as a delay, and if conditions match will Highlight the delay dates.

To begin the procedure,
Start with selecting the cell or cell range to Highlight the delayed deliveries based on another cell date.
➤I selected the cell range D4:E10
Now, open the Home tab >> from Conditional Formatting >> select New Rule

Find Delay using Conditional Formatting Based on Another Cell Date

➤ 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.

=AND($E4>=$H$4,$E4 >$H$5)
Here, the AND function will check whether the cell E4 is greater than equal to H4 then it will also check E4 is greater than H5. If both conditions are fulfilled, then it will Highlight the cell date.

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

Find Delay using Conditional Formatting Based on Another Cell Date

Finally, click OK.
Hence, It will Highlight the cell values of the Order Date and Delivery Date columns where the date is greater than the estimated date of 3/13/2021.

Find Delay using Conditional Formatting Based on Another Cell Date


Similar Readings:


3. Formatting Using Less Than Equal

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

Using Less Than Equal in Conditional Formatting Based on Another Cell Date

➤ 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.

=E4<=$H$5
Here, the less than equal operator will check whether cell E4 is less than equal H5. If the condition is fulfilled, then it will Highlight the cell date.

➤ 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.

Using Less Than Equal in Conditional Formatting Based on Another Cell Date

4. Using DATEVALUE Function in Conditional Formatting Based On Another Cell Date

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

Using DATEVALUE Function in Conditional Formatting Based On Another Cell Date

➤ 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.

=$E4<=DATEVALUE("3/13/2021")

Here, the DATEVALUE function will check whether cell E4 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.

Using DATEVALUE Function in Conditional Formatting Based On Another Cell Date

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.

Practice Section

I’ve provided a practice sheet to practice the explained methods.

Conclusion

In this article, I’ve explained 4 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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo