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.
=(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.
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.
=AND($C5>=$F$5,$C5<=$F$6)
- 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)
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.
=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"))
- 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.