Conditional Formatting Based on Date Range in Excel

We can highlight cells with dates earlier than the current date or within a date range earlier than the current date using conditional formatting in Excel. Using conditional formatting, you can highlight specific cells in a worksheet by giving them a color based on guidelines or conditions. Therefore, this article will demonstrate 4 ways of conditional formatting based on the date range.

Contents hide

📁 Download Excel File

Learn to Apply Conditional Formatting Based on Date Range in Excel with These 4 Methods

1. Using Basic Conditional Formatting Feature

2. Basic Formula Approach

3. Applying AND & TODAY Functions

4. Using OR & TODAY Functions

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

Download the Excel file used for the demonstration from the link below.

Conditional Formatting Based on Date Range.xlsx

Learn to Apply Conditional Formatting Based on Date Range in Excel with These 4 Methods

First, let us get familiarized with the dataset used as an example throughout the article. We have a dataset of project records that includes five projects’ starting and ending dates and their deadlines. Using Conditional Formatting, we want to identify which project’s deadline has already passed. Accordingly, we will show you 4 ways of conditional formatting based on the date range in Excel.

Approach 1

1. Using Basic Conditional Formatting Feature

In this method, we will highlight the dates that fall between the start and end dates. For this, we used the Between from the basic drop-down options here. Let us have a look at the steps.

⬇️⬇️ STEPS ⬇️⬇️

First, select the range C6:C10. Then, go to Conditional Formatting of the Home tab in the Ribbon, and select Between from the Highlight Cell Rules.

Next, a Between named box will pop up. Insert $C$12 and $C$13 for indicating the values to highlight and choose formatting according to your choice from Custom Format and click on OK.

Now, we will have the dates in between the range highlighted.

Approach 2

2. Basic Formula Approach

In this second method, we employed a simple formula to apply as a condition to format the deadline that has passed. So, let us see the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

First, select the range C6:C10. Then, select New Rule from the Conditional Formatting of the Home tab in the Ribbon.

Next, New Formatting Rule named box will pop up. Select “Use a formula to determine which cells to format” as the rule type and enter the formula in “Format values where this formula is true”. Choose the format according to your desire and press OK.

=(C6<=$C$13)*(C6>=$C$12)

Approach 3

3. Applying AND & TODAY Functions

We will apply AND and TODAY functions as a condition for formatting based on the date range. The AND function determines if all conditions are TRUE in the test. On the other hand, the TODAY function returns the current date’s serial number and Excel calculates the date and time using the serial number as the date-time code. Let us see the steps now.

⬇️⬇️ STEPS ⬇️⬇️

First, select the range C6:C10. Then, select New Rule from the Conditional Formatting of the Home tab in the Ribbon.

Next, New Formatting Rule named box will pop up. Select “Use a formula to determine which cells to format” as the rule type and enter the formula in “Format values where this formula is true”. Choose the format according to your desire and press OK.

=AND($C6<=TODAY(), TODAY()-$C6<=25)

🔨 Formula Breakdown

Here, the formula we inserted is:

AND($C6<=TODAY(), TODAY()-$C6<=25)

👉 Here, the TODAY function returns the date code of the day in which the calculation is done.

👉 Then, $C6<=TODAY() is the first condition which checks that if the date in cell C6 is less than or equal to today’s date, and TODAY()-$C6<=25 is the second condition which checks if the deadline falls into last 25 days from today.

👉 Lastly, AND function returns TRUE if both condition are met.

Approach 4

4. Using OR & TODAY Functions

This method will apply OR and TODAY functions as a condition for formatting based on the date range. The OR function determines if any condition is TRUE in the test. On the other hand, the TODAY function returns the current date’s serial number and Excel calculates the date and time using the serial number as the date-time code. Let us see the steps now.

⬇️⬇️ STEPS ⬇️⬇️

First, select the range C6:C10. Then, select New Rule from the Conditional Formatting of the Home tab in the Ribbon.

Next, New Formatting Rule named box will pop up. Select “Use a formula to determine which cells to format” as the rule type and enter the formula in “Format values where this formula is true”. Choose the format according to your desire and press OK.

=OR($C6<=TODAY(), TODAY()-$C6>=25)

🔨 Formula Breakdown

Here, the formula we inserted is:

OR($C6<=TODAY(), TODAY()-$C6>=25)

👉 Here, the TODAY function returns the date code of the day in which the calculation is done.

👉 Then, $C6<=TODAY() is the first condition which checks that if the date in cell C6 is less than or equal to today’s date and TODAY()-$C6<=25 is the second condition which checks if the deadline falls into last 25 days from today.

👉 Lastly, OR function returns TRUE if any of the conditions is met.

Lastly, the outcome looks like this.

📄 Important Notes

🖊️ TODAY function returns the serial number of the current date which is the date-time code used by Excel for calculations.

🖊️ After entering the function, Excel changes the cell format to Date from General. To see the serial number of dates, you must change the cell format to General or Number.

📝 Takeaways from This Article

📌 This article demonstrated 4 methods of conditional formatting based on date range.

📌 In the first method, we used HIGHLIGHT to format conditionally based on date range in Excel.

📌 Then, we employed a simple formula for conditional formatting based on date range.

📌 After that, we used AND & TODAY functions to format conditionally based on date range.

📌 Finally, we employed OR & TODAY functions for conditional formatting based on date range in Excel.

Conclusion

This article has demonstrated 4 methods of conditional formatting based on date range in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

(Visited 63 times, 1 visits today)