Excel Conditional Formatting Based on Date Range

In this tutorial, we will discuss methods for highlighting dates using Conditional Formatting (based on Date Range) in Excel. Sometimes, when we work with spreadsheets containing a long list of dates, we may need to highlight dates that fall within a certain date range.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


4 Methods to Apply Conditional Formatting Based on Date Range

1. Use Simple Formula to Apply Conditional Formatting in Excel based on a Date Range

We can highlight cells that fall within a date range just by using a simple formula. For example, we have a dataset (B4:C13) of dates and we want to highlight dates that are between two given dates. Here, we have used two particular dates as our date range (Start Date and End Date). So, here are the steps.

STEPS:

  • First, select the dates (C5:C13).

Use Simple Formula to Apply Conditional Formatting in Excel based on a Date Range

  • Next, go to Home > Conditional Formatting.

Use Simple Formula to Apply Conditional Formatting in Excel based on a Date Range

  • Now from the Conditional Formatting drop-down, select New Rule.

Use Simple Formula to Apply Conditional Formatting in Excel based on a Date Range

  • Then, select the “Use a formula to determine which cells to format” option from the Rule Type section.
  • Write down the following formula in the Rule Description box:
=(C5<=$F$6)*(C5>=$F$5)

Use Simple Formula to Apply Conditional Formatting in Excel based on a Date Range

  • Go to the Format section.
  • After that, choose the Fill color for highlighting cells and click OK.

Use Simple Formula to Apply Conditional Formatting in Excel based on a Date Range

  • When the formatting is done, again click OK.

  • Finally, all the dates that fall between the Start Date and End Date are highlighted.

Read more: How to Do Conditional Formatting Highlight Row Based On Date


2. Apply Conditional Formatting Based on Date Range in Excel (Between Option)

Another easy way to highlight dates between two specified dates is to apply the Highlight Cells Rules option from the Conditional Formatting drop-down. For instance, in the following example, we will use the ‘Highlight Cells Rules’ option from Conditional Formatting.

STEPS:

  • First, select the dates (C5:C13).

Apply Conditional Formatting Based on Date Range in Excel (Between Option)

  • Go to Home > Conditional Formatting > Highlight Cells Rules > Between.

Apply Conditional Formatting Based on Date Range in Excel (Between Option)

  • Then the Between window will show up. Put your Start  Date and End Date as Cell Reference in the window.

Apply Conditional Formatting Based on Date Range in Excel (Between Option)

  • Now, choose a highlight color.
  • After you choose a color, click OK.

Apply Conditional Formatting Based on Date Range in Excel (Between Option)

  • At last, you will get the following highlighted cells.


Similar Readings:


3. Use Excel AND Function to Apply Conditional Formatting Based on Date Range

We can use functions too while using Conditional Formatting on dates. In this method, we will use the AND function to set the rule. For example, we have a dataset (B4:C13) of dates and we want to highlight dates between today and forty (40) days from now. So, we will follow the below steps to do the task.

STEPS:

  • Select the dates (B4:C13) at first.

Use Excel AND Function to Apply Conditional Formatting Based on Date Range

  • Secondly, go to Home > Conditional Formatting > New Rule.
  • Then choose the rule type ‘Use a formula to determine which cells to format’.
  • Thirdly, write down the following formula in the Rule Description box.
=AND($C5>=TODAY(), $C5-TODAY()<=40)

Use Excel AND Function to Apply Conditional Formatting Based on Date Range

Here, we have used the TODAY function to get the start date of the date range, which is the current date. On the other hand, the AND function checks whether all arguments are TRUE or FALSE, and returns TRUE if all arguments are TRUE.

  • Finally, after choosing the format color, the following dataset is the output.

Note:

We can choose other date ranges by using the above formula. For instance, we can highlight dates that fall between today and the past 40 days.

Read more: Excel Conditional Formatting for Dates within 30 Days


4. Using OR Function in Excel Conditional Formatting Based on Date Range

Likewise, in the previous method, we can use the OR function to set the rule in Conditional Formatting in Excel. Let us go through the steps.

STEPS:

  • First, select the dataset.

Using OR Function in Excel Conditional Formatting Based on Date Range

  • Go to Home > Conditional Formatting > New Rule.
  • Then choose the rule type ‘Use a formula to determine which cells to format’.
  • Now, write down the following formula in the Rule Description box.
=OR($C5<=TODAY(), $C5-TODAY()>=40)

Using OR Function in Excel Conditional Formatting Based on Date Range

Here, the OR function checks whether any of the arguments are true, and returns TRUE or FALSE. This function returns FALSE only if all arguments are FALSE. Besides, same as the previous method, we have used the TODAY function to specify the Start Day.

  • After writing the formula, do the format tasks as we did in Method 1 and click OK.
  • Finally, we will get the highlighted cells as we expected.


Conclusion

In the above discussion, I have used the simplest and quickest method to highlight dates with Conditional Formatting in a date range. Hopefully, you will find these methods helpful. Download the workbook and try these methods. Let me know if you have any queries regarding the methods I have mentioned here.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo