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