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.

Excel Conditional Formatting Based on Date Range


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.

List of Employees

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

Selecting Cells for Conditional Formatting

  • Next, go to Home > Conditional Formatting.

Selecting Conditional Formatting from Home Tab

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

Choosing New Rule from Conditional Formatting

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

Applying Formula to New Formatting Rule

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

Choosing Background Color from Fill Tab

  • Lastly, when the formatting is done, again click OK.
  • Finally, all the dates that fall between the Start Date and End Date are highlighted.

Final Output of Using Simple Formula

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

Selecting Cell Range

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

Choosing Between Option from Conditional Formatting

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

Inserting Cell Reference and Highlighting Format

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

Final Output of Applying Between Option


Similar Readings:


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)

Applying AND Function in New Formatting Rule

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.

Result of Using AND Function Based on Date Range in Conditional Formatting

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)

Inserting OR Function Based on Date Range

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.

Output of Using OR Function in Conditional Formatting

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"))

Combination of AND with DATEVALUE Function Based on Cell Range

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.

Final Output of Conditional Formatting Based on Cell Range


Practice Section

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

Dataset to Practice By Yourself


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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo