# Excel Conditional Formatting Based on Date Range (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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. ## 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.
`=(C5<=\$F\$6)*(C5>=\$F\$5)` • 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. ### 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. ### 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.
`=AND(\$C5>=\$F\$5,\$C5<=\$F\$6)` Here, we have used the AND function to check whether all arguments (\$C5>=\$F\$5,\$C5<=\$F\$6) are TRUE or FALSE, and returns TRUE if all arguments are TRUE.
• 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. 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 or future given dates. For this, go through the following formulas based on your preferred condition where the TODAY function is used to get the start date of the date range.

• When 40 to 10 days before: `=AND(TODAY()-\$C5>=10, TODAY()-\$C5<=40)`
• When less than 10 days ago: `=AND(TODAY()-\$C5>=1, TODAY()-\$C5<10)`
• Between 40 to 10 days: `=AND(\$C5-TODAY()>=10, \$C5-TODAY()<=40)`
• After less than 10 days: `=AND(\$C5-TODAY()>=1, \$C5-TODAY()<10)`

### 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.
`=OR(\$C5<=\$F\$5, \$C5>=\$F\$6)` Here, the OR function checks whether any of the arguments (\$C5<=\$F\$5, \$C5>=\$F\$6) are true, and returns TRUE or FALSE. This function returns FALSE only if all arguments are FALSE.

• 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. Note: We can also the TODAY function to specify the Start Date and therefore it creates the following formula.

`=OR(\$C5<=TODAY(), \$C5-TODAY()>=40)`

### 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.
`=AND(\$C5>DATEVALUE("7/1/2021"),\$C5<DATEVALUE("12/31/2021"))` Here, we have used the AND function to check whether all arguments are TRUE or FALSE, and return TRUE if all arguments are TRUE. Along with this, the DATEVALUE function converts the date (given date ranges) as a text value. As a result, Excel recognizes it as a date.
• 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 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 Advanced Excel Exercises with Solutions PDF  