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.

Overview of Excel Conditional Formatting Based on Another Cell Date


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.

Sample Dataset

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

Excel 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($E5>=$H$5,$E5<=$H$6)

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.

Apply New Rule

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

Format Cells Dialog Box

Then, click OK.

Close New Formatting Rule Dialog Box

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.

Excel Conditional Formatting Based on Another Cell Date

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


Similar Readings:


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

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

=E5<=$H$6
Here, the less than equal operator will check whether cell E5 is less than equal H6. 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.

Apply New Rule

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.

Excel Conditional Formatting Based on Another Cell Date


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

Apply Conditional Formatting

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

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

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.

New Formatting Rule Dialog Box

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.

Excel Conditional Formatting Based on Another Cell Date


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

Shamima Sultana

Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in 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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo