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
=$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.
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
=$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.
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
=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
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
=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
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(D5>TODAY(),IF(E5>5000,"Matched","Not Matched"),"Not Matched")
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
=$F5="Matched"
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
=D5-C5
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
=$F5>6
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
=$D5=""
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
=$D5<>""
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
=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.
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
=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.
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.