Excel Conditional Formatting Based on Date Range (5 Methods)

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. In this tutorial, we will discuss 5 useful methods to apply Conditional Formatting based on Date Range in Excel.

Download Practice Workbook

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

5 Useful Methods to Apply Conditional Formatting Based on Date Range in Excel

To describe the methods, here is a sample dataset. it shows the information of 9 Employee Names and their Joining Dates in a company. the values are placed in the Cell Range B5:C13.

Now, let’s follow the methods below to apply conditional formatting based on date range.

1. Use Simple Formula in Conditional Formatting Based on Date Range

We can highlight cells that fall within a date range just by using a simple formula. For example, we have a dataset where 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.

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

• Next, go to Home > Conditional Formatting.

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

• Then, select the “Use a formula to determine which cells to format” option from the Rule Type section.
• Here, write down the following formula in the Rule Description box.

• Next, go to the Format section.
• After that, choose any Background Color from the Fill tab for highlighting cells and click OK.

• Lastly, 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 Between Option in Conditional Formatting for Date Range in Excel

Another easy way to highlight dates between two specified dates is to apply the Highlight Cells Rules option from the Conditional Formatting drop-down.  Along with this, we changed the date range for variation. You can insert your preferred range according to your choice. Let’s see the steps below.

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

• Then, go to Home > Conditional Formatting > Highlight Cells Rules > Between.

• Afterward, the Between window will show up.
• Here, put your Start  Date and End Date as Cell References in the window.
• Now, choose a highlight color selecting Custom Format.
• After that, choose a color, and click OK.

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

Similar Readings:

• How to Use Conditional Formatting in Excel Based on Dates
• 4 Quick Excel Formula to Change Cell Color Based on Date
• Excel Conditional Formatting Dates

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

We can use Excel functions too while using Conditional Formatting on dates. In this method, we will use the AND function to set the rule. So, we will follow the below steps to do the task.

• Firstly, select the dates from the Cell range C5:C13.
• 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.

• After writing the formula, do the format tasks as we did in Method-1 and click OK.
• Finally, after choosing the format color, the following dataset is the output.

Read more: Excel Conditional Formatting for Dates within 30 Days

4. Insert OR Function in Excel Conditional Formatting for 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.

• First, select the Joining Dates from the dataset.
• Afterward, 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.

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

5. Combine AND with DATEVALUE Function to Highlight Dates Based on Range

In this last method, we will use the DATEVALUE function along with the AND function to set the rule in Conditional Formatting in Excel. Let us go through the steps.

• First, select the Joining Dates from the dataset.
• Afterward, 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.

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

Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Conclusion

In the above discussion, we described 5 useful methods to apply Conditional Formatting based on Date Range in Excel. 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. Follow ExcelDemy for more tutorials.

Related Articles

• Excel Conditional Formatting Based on Date in Another Cell
• Apply Conditional Formatting to the Overdue Dates in Excel (3 Ways)
• Excel Conditional Formatting Dates Older than Today (3 Simple Ways)
• Excel Conditional Formatting Based On Another Cell Date (4 Ways)