How to Do Conditional Formatting Highlight Row Based On Date

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 to know the ways of doing Conditional Formatting based on another cell range.

Download Workbook

11 Ways To Do Conditional Formatting Highlight Row Based On Date

Here, I have the two data tables to demonstrate the ways of Conditional Formatting based on dates in Excel. The first table has Sales value, Order Date and Delivery Date for different items of a company and the second table contains Working Hours for first 10 days of January month for some workers of a company. The date format here is mm/dd/yyyy.

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

Method-1: Using Highlight Cells Rules Option for Conditional Formatting Highlight Row Based On Date

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.

Step-01:
➤Select the data range on which you want to apply the Conditional Formatting
➤Go to Home Tab>>Conditional Formatting Dropdown>>Highlight Cells Rules Groups>>A Date Occurring Option.

Then, A Date Occurring Wizard will open.
➤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).

Result:
After that, the last month’s Order Dates will be highlighted as below.

Read more: Excel Conditional Formatting Based on Date Range

Method-2: Highlighting Specific Dates for Conditional Formatting Highlight Row Based On Date

Let’s say, you want to highlight the rows having a specific date for this case which is 11/25/2021, and to do this you can use the DATEVALUE function.

Step-01:
➤Select the data range on which you want to apply the Conditional Formatting
➤Go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.

Then the New Formatting Rule Wizard will appear.
➤Select Use a formula to determine which cells to format option.

➤Click on Format Option.

After that, the Format Cells Dialog Box will open up.
➤Select Fill Option
➤Choose any Background Color
➤Click on OK.

After that, the Preview Option will be shown as below.

Step-02:
➤Write the following formula in the Format values where this formula is true: Box

When the cells of Column C will be Equal to the date 11/25/2021, then the Conditional Formatting will appear in those rows.

DATEVALUE will convert the text date into a value.

➤Press OK.

Result:
Now, you will get the rows having the specific date 11/25/2021 highlighted.

Read more: How to Use Conditional Formatting in Excel Based on Dates

Method-3: Highlighting Dates Using TODAY Function

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

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

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

➤Press OK.

Result:
In this way, you will get the rows having dated one week before today highlighted (Today’s date is 12/14/2021).

Read more: Excel Conditional Formatting Dates

Method-4: Highlight 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 after today and sales value greater than $5,000.00, and to do this you can use the AND function here.

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

When the dates of Column D will be Greater than TODAY() (gives today’s date) and the sales values of Column E will be Greater than 5000, then the Conditional Formatting will appear in those rows.

➤Press OK

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

Read more: Excel Conditional Formatting Based On Another Cell Date

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

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

Let’s say, you want to highlight the rows which have delivery dates after today or sales value greater than $5,000.00 and to do this you can use the OR function here.

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

When the dates of Column D will be Greater than TODAY() (gives today’s date) or the sales values of Column E will be Greater than 5000, then the Conditional Formatting will appear in those rows.

➤Press OK

Result:
Afterward, you will get the rows fulfilling any one conditions highlighted.

Similar Readings:

• Excel Conditional Formatting Based on Date in Another Cell
• Apply Conditional Formatting to the Overdue Dates in Excel (3 Ways)
• How to Highlight Row Using Conditional Formatting (9 Methods)
• 4 Quick Excel Formula to Change Cell Color Based on Date

Method-6: 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.

Step-01:
➤Select the output Cell F5.
➤Type the following formula

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

➤Press ENTER
➤Drag down the Fill Handle Tool.

Now, we will get Matched only for the last row where two conditions have met, and then we will highlight this row.

Step-02:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

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

➤Press OK

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

Read more: Excel Conditional Formatting Formula with IF

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 greater than 6 (for highlighting the late delivery rows) and to do this you can follow this method. For this purpose, we have added a column named Gap.

Step-01:
➤Select the output Cell F5.
➤Type the following formula

It will return the gaps between the two dates (Delivery Date and Order Date).

➤Press ENTER
➤Drag down the Fill Handle Tool.

Now, we will get the gaps between these dates and we have two rows with gaps of more than 6 and then we will highlight these rows.

Step-02:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

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

➤Press OK

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

Read more: Excel Conditional Formatting Dates Older than Today

Method-8: Highlighting Based On Empty Dates

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) that means not delivered yet, then you can follow this method.

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

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

➤Press OK.

Result:
Then, you will get the rows highlighted when the corresponding cells of the column Delivery Date will be empty.

Read more: Conditional Formatting for Blank Cells in Excel

Method-9: Highlighting Based On Non-Empty Dates

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

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

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

➤Press OK.

Result:
Then, 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 Conditional Formatting

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

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

$B5 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.

➤Press OK.

Result:
In this way, you will get the rows highlighted for weekends.

Method-11: Highlighting Special Dates Using Conditional Formatting

Here, we will highlight the rows for some special dates which have been shown below on the right side.

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

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

$B5 is the corresponding date value and $G$7:$G$8 is the range of special dates and 0 is for an exact match.

When the dates match with the range $G$7:$G$8, then the Conditional Formatting will appear in the corresponding rows.

➤Press OK.

Result:

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

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Conclusion

In this article, I tried to cover the easiest ways to do Conditional Formatting highlight row 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.

Further Readings

• Conditional Formatting with Formula for Multiple Conditions in Excel
• How to Use Conditional Formatting in Excel [Ultimate Guide]
• Excel Conditional Formatting for Dates within 30 Days (3 Examples)
• Excel Conditional Formatting on Multiple Columns