Highlighting Row with Conditional Formatting Based on Date in Excel

In this article, I am going to show you how to highlight rows with Conditional Formatting Based on Date in Excel. If you are looking for some of the easiest ways to do Conditional Formatting highlight row based on date in Excel, then you are in the right place. Sometimes, it becomes necessary to highlight a row based on dates while working with a large dataset in Excel. This article will help you know how Conditional Formatting is based on another cell range. Please take a quick look at the methods and the formula we are going to use in the conditional formatting and their corresponding outputs.

In this section, I am going to show you 11 easy and simple methods to highlight row with conditional formatting based on Date in Excel. To illustrate, here, I have a data table to demonstrate the ways to highlight row with Conditional Formatting based on dates in Excel. The table has the Sales value, Order Date, and Delivery Date for different items of a company The date format here is dd/mm/yyyy.

For this purpose, I have used Microsoft Excel 365 version, you can use any other version according to your convenience.

Method-1: Using Highlight Cells Rules Option to Conditionally Format Rows Based on Date in Excel

Here, we will highlight the rows having Order Dates of the last month by using the built-in Highlight Cells Rules option of Conditional Formatting.

Steps:

Firstly, select the data range on which you want to apply the Conditional Formatting. Here, I am selecting C5:C11.

Then, go to Home Tab. From here, click on Conditional Formatting Dropdown >> Highlight Cells Rules Groups >> A Date Occurring Option.

Consequently, A Date Occurring Wizard will open.
From here, select Last Month Option from the left-side box Dropdown and Light Red Fill Option from the right-side box Dropdown (or any other option).

As a result, the last month’s Order Dates will be highlighted as below.

Method-2: Highlighting Specific Dates with Excel Conditional Formatting

Let’s say, you want to highlight the rows having a specific date. To do this, you can use the DATEVALUE function. Here for this case, we are taking the date as 25-11-2021.

Steps:

Firstly, select the data range on which you want to apply the Conditional Formatting.
Then, go to Home Tab >> Conditional Formatting Dropdown >> New Rule Option.

Then the New Formatting Rule Wizard will appear.
From here, select Use a formula to determine which cells to format option.

Now, click on Format Option.

After that, the Format Cells Dialog Box will open up.
Then, select Fill Option
After that, choose any Background Color.
Next, click on OK.

After that, the Preview Option will be shown below.

Then, write the following formula in the Format values where this formula is true: Box

=$C5=DATEVALUE("25/11/2021")

When the cells of Column C are Equal to the date 11/25/2021, then the Conditional Formatting will appear in those rows. DATEVALUE function will convert the text date into a value.
Finally, press OK.

As a result, you will get the rows having the specific date 25/11/2021 highlighted.

Method-3: Using TODAY Function to Highlight Dates by Excel Conditional Formatting

Suppose, you want to highlight the dates one week from today in the Order Date column and you can do this by using the TODAY function.

Steps:

First, follow the steps of Method-2 to open the New Formatting Rule dialogue box from the Conditional Formatting option on the Home tab ribbon.

Now, type the following formula in the Format values where this formula is true: Box

=$C5<TODAY()-7

When the dates of Column C are Less than TODAY()-7 (one week before today), then the Conditional Formatting will appear in those rows.
Then, press OK.

In this way, you will get the rows dated one week before today highlighted (Today’s date is 29-01-2023).

Note: If you want to find values older than 1 year, your formula will be:-

=$C5<TODAY()-365

Method-4: Highlighting Row Based on Date for Multiple Conditions Using AND Function

While dealing with multiple conditions you can use the AND function, this will highlight the rows only when both conditions are met. Assume, you want to highlight the rows which have delivery dates before 05/12/2021 and sales value greater than $5,000.00, and to do this you can use the AND function here.

To do that, follow the steps below.

Steps:

First, follow the steps of Method-2 to open the New Formatting Rule dialogue box from the Conditional Formatting option on the Home tab ribbon.
After that, you will get the following New Formatting Rule Dialog Box.

Now, type the following formula in the Format values where this formula is true: Box

=AND($D5<DATE(2021,12,5),$E5>5000)

When the dates of Column D are Greater than 05/12/2021 and the sales values of Column E are Greater than 5000, then the Conditional Formatting will appear in those rows.
Finally, press OK.

After that, you will get the last row fulfilling both conditions highlighted.

Method-5: Using OR Function to Highlight Row Based on Date for Multiple Conditions

For dealing with multiple conditions you can use the OR function as well, unlike the AND function which will highlight the rows if any of the criteria meets.

Let’s say, you want to highlight the rows which have delivery dates before 05/12/2021 or which has sales value greater than $5,000.00. To do this you can use the OR function here.

Steps:

First, follow the steps of Method-2 to open the New Formatting Rule dialogue box from the Conditional Formatting option on the Home tab ribbon.

After that, you will get the following New Formatting Rule Dialog Box. Now, type the following formula in the Format values where this formula is true: Box

=OR($D5<DATE(2021,12,5),$E5>5000)

When the dates of Column D are Greater than 5/12/2021 or the sales values of Column E are Greater than 5000, then the Conditional Formatting will appear in those rows.

Finally, press OK

Afterward, you will get the rows fulfilling any one condition highlighted.

Method-6: Applying Excel Conditional Formatting Based on Date for Multiple Conditions Using IF Function

Here, for highlighting rows that have fulfilled multiple conditions we are using the IF function. For this purpose, we have added a column named Helper.

Here, our target is the same as 3rd method. That is we want to highlight the rows which have delivery dates before 05/12/2021 and sales value greater than $5,000.00. We can use the helper column and the IF function to do that. To know more, follow the steps below.

Steps:

Firstly, select the output Cell F5.
Now, type the following formula and, press ENTER.

=IF($D5<DATE(2021,12,5),IF($E5>5000,"Matched","Unmatched"),"Unmatched")

Here, IF will return “Matched” if both the conditions are met, otherwise “Not Matched”.

After that, drag down the Fill Handle Tool. As a result, we will get Matched only for the first & last rows where two conditions have been met.

Now, our target is to highlight those rows that contain Matched in the Helper column.
To do that, first, follow the steps of Method-2 to open New Formatting Rule Dialog Box.

Now, type the following formula in the Format values where this formula is true: Box

=$F5="Matched"

When the values of Column F are Equal to “Matched”, then the Conditional Formatting will appear in those rows.
Finally, press OK.

Afterward, you will get the last row fulfilling both conditions highlighted.

Method-7: Conditional Formatting Based on Gaps Between Dates

Suppose, you want to highlight the rows having a difference between the Delivery Date and Order Date less than 6 (for highlighting the fast delivery rows) and to do this you can follow this method. For this purpose, we have added a column named Gap.

Steps:

First, select the output Cell F5.
Then, type the following formula and press ENTER.

=D5-C5

As a result, it will return the gaps between the two dates (Delivery Date and Order Date).

Now, drag down the Fill Handle Tool.

Now, we will highlight these rows where the gap is less than 6.
To do that, follow the steps of Method-2 to open New Formatting Rule Dialog Box.

Type the following formula in the Format values where this formula is true: Box

=$F5<6

When the values of Column F are less than 6, then the Conditional Formatting will appear in those rows.

Now, press OK

Afterward, you will get the rows highlighted having gaps between the Delivery Dates and Order Dates more than 6.

Method-8: Highlighting Rows Based on Empty Dates with Excel Conditional Formatting

If you want to highlight the rows corresponding to the Delivery Dates which are empty (for explaining this method I have removed the dates from the two cells of the Delivery Date column) which means not delivered yet, then you can follow this method.

Steps:

Firstly, follow the steps of Method-2 to open New Formatting Rule Dialog Box.

Now, type the following formula in the Format values where this formula is true: Box

=$D5=""

When the cells of Column D are Equal to Blank, the Conditional Formatting will appear in the corresponding rows.
Finally, press OK.

As a result, you will get the rows highlighted when the corresponding cells of the column Delivery Date will be empty.

Method-9: Applying Conditional Formatting to Rows Based on Non-Empty Dates

For highlighting rows corresponding to the Delivery Dates which are non-empty in the previous data set, then you can follow this method.

Steps:

Firstly, select cells B5:E11 and follow the steps of Method-2 to open New Formatting Rule Dialog Box.

Now, type the following formula in the Format values where this formula is true: Box

=$D5<>""

When the cells of Column D will be Not Equal to Blank, the Conditional Formatting will appear in the corresponding rows.
Then, press OK.

As a result, you will get the cells of the rows highlighted when the corresponding cells of the column Delivery Date will be non-empty.

Method-10: Highlighting Weekends Using Excel Conditional Formatting

Suppose, you want to highlight the rows having dates whose corresponding days are Saturday and Sunday which means weekends for this company, and to do this you can use the WEEKDAY function.

Steps:

Firstly, select cells B5:E11 and follow the steps of Method-2 to open New Formatting Rule Dialog Box.
Now, type the following formula in the Format values where this formula is true: Box

=WEEKDAY($D5,2)>5

Here, $D5 is the corresponding date value and 2 is for Numbers 1(Monday) through 7(Sunday). So, WEEKDAY will return 6 and 7 for Saturday and Sunday respectively.
When the values will be 6 and 7, then the Conditional Formatting will appear in the corresponding rows.

Now, press OK.

As a result, in this way, you will get the rows highlighted for weekends.

Method-11: Applying Excel Conditional Formatting to Highlight Special Dates

Here, we will highlight the rows for a special date which has been shown below on the right side. Let’s say, the customers who ordered on 25/11/2021 will get a special discount.

So, our aim is to highlight the rows that match the ordered date that matches with 25/11/2021.

Steps:

Firstly, select cells B5:E11 and follow the steps of Method-2 to open New Formatting Rule Dialog Box.
Now, type the following formula in the Format values where this formula is true: Box

=MATCH($G$5,$C5,0)

$G$5 is the corresponding date value and $C5 is the range of special dates (we locked the column C, so the range is C5:C11 and 0 is for an exact match.
When the dates match with the range C5:C11, then the Conditional Formatting will appear in the corresponding rows.
Now, press OK.

In this way, you will get the rows highlighted for special dates.

Download Practice Workbook

Highlight Rows Based Dates.xlsx

Conclusion

In this article, I tried to cover the easiest ways to highlight row with Conditional Formatting based on date in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Highlighting Row with Conditional Formatting Based on Date in Excel

Method-1: Using Highlight Cells Rules Option to Conditionally Format Rows Based on Date in Excel

Method-2: Highlighting Specific Dates with Excel Conditional Formatting

Method-3: Using TODAY Function to Highlight Dates by Excel Conditional Formatting

Method-4: Highlighting Row Based on Date for Multiple Conditions Using AND Function

Method-5: Using OR Function to Highlight Row Based on Date for Multiple Conditions

Method-6: Applying Excel Conditional Formatting Based on Date for Multiple Conditions Using IF Function

Method-7: Conditional Formatting Based on Gaps Between Dates

Method-8: Highlighting Rows Based on Empty Dates with Excel Conditional Formatting

Method-9: Applying Conditional Formatting to Rows Based on Non-Empty Dates

Method-10: Highlighting Weekends Using Excel Conditional Formatting

Method-11: Applying Excel Conditional Formatting to Highlight Special Dates

Conclusion

Further Readings

Leave a reply Cancel reply