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.

excel conditional formatting highlight row based on date

excel conditional formatting highlight row based on date

 

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.

excel conditional formatting highlight row based on date

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.

Highlight Cells Rules

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

Highlight Cells Rules

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

excel conditional formatting highlight row based on date

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.

excel conditional formatting highlight row based on date

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.

specific dates

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

specific dates

➤Click on Format Option.

excel conditional formatting highlight row based on date

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

specific dates

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

specific dates

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

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

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.

specific dates

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

excel conditional formatting highlight row based on date

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.

excel conditional formatting highlight row based on date

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

TODAY function

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

=$C5<TODAY()-7

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.

TODAY function

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

excel conditional formatting highlight row based on date

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.

excel conditional formatting highlight row based on date

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

AND function

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

=AND($D5>TODAY(),$E5>5000)

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

AND function

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

excel conditional formatting highlight row based on date

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.

excel conditional formatting highlight row based on date

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

OR function

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

=OR($D5>TODAY(),$E5>5000)

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

OR function

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

excel conditional formatting highlight row based on date


Similar Readings:


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.

excel conditional formatting highlight row based on date

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

=IF(D5>TODAY(),IF(E5>5000,"Matched","Not Matched"),"Not Matched")

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

IF function

➤Press ENTER
➤Drag down the Fill Handle Tool.

IF function

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

IF function

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

IF function

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

=$F5="Matched"

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

➤Press OK

IF function

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

excel conditional formatting highlight row based on date

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.

excel conditional formatting highlight row based on date

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

=D5-C5

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

highlighting based on gaps

➤Press ENTER
➤Drag down the Fill Handle Tool.

highlighting based on gaps

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.

excel conditional formatting highlight row based on date

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

highlighting based on gaps

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

=$F5>6

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

➤Press OK

highlighting based on gaps

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

excel conditional formatting highlight row based on date

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.

excel conditional formatting highlight row based on date

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

Empty dates

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

=$D5=""

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

➤Press OK.

Empty dates

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

excel conditional formatting highlight row based on date

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.

excel conditional formatting highlight row based on date

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

Non-Empty dates

➤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 to the corresponding rows.

➤Press OK.

Non-Empty dates

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

excel conditional formatting highlight row based on date

 

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.

excel conditional formatting highlight row based on date

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

highlighting weekend

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

=WEEKDAY($B5,2)>5

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

highlighting weekend

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

excel conditional formatting highlight row based on date

 

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.

excel conditional formatting highlight row based on date

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

highlighting specific dates

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

=MATCH($B5,$G$7:$G$8,0)

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

highlighting specific dates

Result:

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

excel conditional formatting highlight row based on date

 

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.

Practice

 

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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo