Excel Conditional Formatting Based on Date in Another Cell

Conditional formatting is a useful Excel feature that allows you to show data in a more organized fashion. It is extremely helpful for displaying date information as well. In this article, I have provided 3 ways for conditional formatting cells using date information from other cells.

This article is strictly focused on the conditional formatting of dates. If you want to learn about conditional formatting in general, then you check out this article.

Download the Workbook

You can download the workbook that I used in this article from below and practice with it by yourself.

3 Ways to Conditionally Format Cells Based on Other Cells

1. Based on a Date in a Particular Cell

This method shows how you can apply conditional formatting in selected cells based on a date in a particular cell.

Here, I have formatted the rows where the joining dates are after a specific date.

Conditional formatting based on a particular cell

Follow these steps for applying this solution:

  1. Select the cells you want to apply the conditional formatting on (In my case, B9:D13).Selecting the data
  2. Go to Home and select the Conditional Formatting option under the Style
  3. Select the New Rule option from the drop-down menu.Selecting the New Rule option
  4. A new window named New Formatting Rule should appear. Select Use a formula to determine which cells to format rule type.
  5. Enter the condition/formula in the specified field (In my case, =$D9>$C$4) and select the Format option.Entering the formula

Explanation: The dollar sign ($) is known as the Absolute Symbol. It makes the cell references absolute and doesn’t allow any changes. You can lock a cell by selecting the cell and pressing the F4 button.

Here, $C$4 means that the C4 cell is locked for every reference. It will not change. The $D9 indicates that the column D reference is absolute, it will not change, but the rows will adjust accordingly.

So, =$D9>$C$4 this formula will output the rows where the joining dates of employees (D column) are after the specific date (C4 Cell).

  1. A new window named Format Cells should appear. Select your desired format and click OK (In my case, Font Style: Bold, Fill: Yellow).Selecting the Bold FontSelecting the Yellow Fill color

It will format the selected cells according to the condition and selected format.

Read more: Excel Conditional Formatting Based on Another Cell

2. Between Two Dates

This method shows how you can apply conditional formatting in selected cells based on two dates in different cells.

Here, I have formatted the rows where the joining dates are between two different cell dates.

Conditional formatting between two dates

Follow these steps for applying this solution:

  1. Select the cells you want to apply the conditional formatting on (In my case, B11:D15).Selecting the data
  2. Now, go to the New Formatting Rule window by following the Step-2 and Step-3 of Method-1. Select Use a formula to determine which cells to format rule type.
  3. Enter the condition/formula in the specified field (In my case, =AND($D11>=$C$4, $D11<=$C$6)) and select the desired format by following the Step-6 of Method-1 and click OK.Entering the formula

Explanation: The dollar sign ($) is known as the Absolute Symbol. It makes the cell references absolute and doesn’t allow any changes. You can lock a cell by selecting the cell and pressing the F4 button.

Here, =AND($D11>=$C$4, $D11<=$C$6) this formula checks whether the dates in column D are greater than the C4 cell’s date and less than the C6 cell’s date. If the date satisfies the conditions, then it formats the cell).

It will format the selected cells according to the condition and selected format.

Read more: Excel Formula to Change Cell Color Based on Date

3. Based on Dates in Another Column

This method shows how you can apply conditional formatting in selected cells based on another column.

Here, I have formatted the cells and highlighted the date expired products.

Conditional formatting based dates on a another column

Follow these steps for applying this solution:

  1. Select the cells you want to apply the conditional formatting on (In my case, C7:C11).Selecting the data
  2. Now, go to the New Formatting Rule window by following the Step-2 and Step-3 of Method-1. Select Use a formula to determine which cells to format rule type.
  3. Enter the condition/formula in the specified field (In my case, =$D7<TODAY()) and select the desired format by following the Step-6 of Method-1 and click OK.Entering the formula

Explanation: The dollar sign ($)  is known as the Absolute Symbol. It makes the cell references absolute and doesn’t allow any changes. You can lock a cell by selecting the cell and pressing the F4 button.

The TODAY() function outputs the current date, which is, in this case, 21-01-21.

Here, =$D7<TODAY() this formula checks whether the dates in column D are less than the current date. If the date satisfies the conditions, then it formats the cell).

It will format the selected cells according to the condition and selected format.

Conclusion

In Microsoft Excel, conditional formatting is a powerful tool. I have used this feature and narrowed down 3 popular ways of formatting dates in this article.  I hope you find the solution you were looking for. Please leave a comment if you have any suggestions or questions. Thank you.


Further Readings

Chinmoy Mondol

Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo