Home » Excel Basics » Conditional Formatting to Highlight Row Based on Date in Excel

Conditional Formatting to Highlight Row Based on Date in Excel

Sakibul Hasan Nahid Sakibul Hasan Nahid March 23, 2023 2 0

Sometimes we look for some specific data of some specific date and we have to highlight them so that we can find them easily. Here in this article we will illustrate 11 ways to excel conditional formatting highlight row based on date .

Contents hide
📁 Download Excel File
Learn to Do Conditional Formatting to Highlight Row Based on Date in Excel with These 11 Suitable Approaches
1. Applying Highlight Cells Rules Option
2. Utilizing DATEVALUE Function
3. Applying TODAY Function
4. Implementing AND Function
5. Utilizing OR Function
6. Applying IF Function
7. Using Gaps Between Dates
8. Based on Empty Dates
9. Based on Non-Empty Dates
10. Using WEEKDAY Function
11. Applying MATCH Function
📝 Takeaways from This Article
Conclusion
Related Articles

📁 Download Excel File

Download the following Excel file.

Learn to Do Conditional Formatting to Highlight Row Based on Date in Excel with These 11 Suitable Approaches

Here we will learn 11 ways to excel conditional formatting. Using the Conditional Formatting option in the excel we will highlight rows based on dates for different conditions. Here we will use the following dataset given below.

Approach

 1. Applying Highlight Cells Rules Option

In this method, we are going to highlight our desired cells using the Highlight Cells in Conditional Formatting in excel.

Dataset of Conditional Formatting Highlight Row Based on Date

⬇️⬇️ STEPS ⬇️⬇️

  • Select data range.

Using Highlight Cells Rules Option to Do Conditional Formatting Highlight Row Based on Date

  • Then go to the Home Tab and click the Conditional Formatting and click on the Highlight Cells.

Utilizing Highlight Cells Rules Option to Do Conditional Formatting Highlight Row Based on Date

  • After clicking Highlighting cells select A Date Occuring option.
  • After clicking A Date Occuring you will get the following window. Here select the following options and then click OK.

Applying Highlight Cells Rules Option to Do Conditional Formatting Highlight Row Based on Date

  • After clicking OK we will see some cells are highlighted.

Here we can see that the cells highlighted here which contains date from last week. There are many options there you can choose any of them whichever is suitable for you.

📕 Read More: 6 Easy Ways to Highlight Lowest Value in Excel

Approach

2. Utilizing DATEVALUE Function

Here in this method we are going to use the DATEVALUE function to perform excel conditional formatting highlight row based on date. In this method we are going to highlight the rows which contain the same dates.

⬇️⬇️ STEPS ⬇️⬇️

  • At first go to the Method 2 worksheet and select  dataset.

Using DATEVALUE Function to Do Conditional Formatting Highlight Row Based on Date

  • Then Select Conditional Formatting in the Home Tab. After that, select New Rule.

Utilizing DATEVALUE Function to Do Conditional Formatting Highlight Row Based on Date

  • Then click on Use a formula to determine which cells to format.

Applying DATEVALUE Function to Do Conditional Formatting Highlight Row Based on Date

  • After clicking the following option click on Format.

  • After clicking format go to the Fill tab and select the color and click OK.

  • After clicking OK you will get following window.

Using DATEVALUE Function to Do Conditional Formatting Highlight Row Based on Date

  • Enter the following formula and click OK.
=$C6=DATEVALUE(“12/12/2022”)

Utilizing DATEVALUE Function to Do Conditional Formatting Highlight Row Based on Date

  • And you will obtain following result.

Here we can see that we have highlighted the products sold in the shop on same date.

Approach

3. Applying TODAY Function

In this method we will use TODAY function and highlight all the dates 14 days past from today.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Method 3 worksheet and select the dataset.

Using TODAY Function to Do Conditional Formatting Highlight Row Based on Date

  • Now follow the steps you followed in Method 2 in the Conditional Formatting and instead of using the previous formula now use the following formula. Click OK.
=$C6<TODAY()-14

Utilizing TODAY Function to Do Conditional Formatting Highlight Row Based on Date

  • You will get the following result.

Here we can see we have highlighted all the dates 14 days ago from today.

📕 Read More: Conditional Formatting Based on Another Cell Date in Excel

Approach

 4. Implementing AND Function

Here we are going to use AND function for conditional formatting. We will highlight those rows which contain data past today and has sales higher than 2000 USD.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Method 4 worksheet and select the data range.

Utilizing AND Function to Do Conditional Formatting Highlight Row Based on Date

  • Then again follow the steps you followed in Method 2. Use the following formula and click OK.
=AND($C6<TODAY(),$D6>2000)

Using AND Function to Do Conditional Formatting Highlight Row Based on Date

  • And you will obtain following result bellow.

Here we have highlighted those rows which contain data past today and has sales higher than 2000 USD.

Approach

 5. Utilizing OR Function

Here we are going to use OR function for conditional formatting.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Method 5 worksheet and select the data range.

Utilizing OR Function to Do Conditional Formatting Highlight Row Based on Date

  • Then again follow the steps from Method 2. Then enter following formula and click OK.

=OR($C6<TODAY()-10,$D6>2000)

  • Then you get the following result.

Here we have highlighted the rows containing dates either 10 or more days ago from today or sales higher than 2000 USD.

Approach

 6. Applying IF Function

In this method, we will use the IF function for excel formatting.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Method 6 worksheet and here we added another column T/F for logical output.

Utilizing IF Function to Do Conditional Formatting Highlight Row Based on Date

  • Select cell E6.

  • Enter following formula. In this formula, if the conditions are satisfied the formula will return T otherwise F. If the date is less than 14 days past form today and the sales is greater than 2000 USD it will return T as True otherwise F.
=IF(C6<TODAY()-14,IF(D6>2000,”T”,”F”),”F”)

Using IF Function to Do Conditional Formatting Highlight Row Based on Date

  • Press the Enter key.

  • Now select cell E6 and drag Fill Handle icon from E6 to E12.

Applying IF Function to Do Conditional Formatting Highlight Row Based on Date

  • Now for highlighting we want to highlight the rows which are true for that select the data range.

  • Then follow the steps from Method 2, and enter following formula.
=$E6=”T”

  • Then press OK and you will the following results.

Using IF Function to Do Conditional Formatting Highlight Row Based on Date

Here you can see that we only highlighted the rows which are true.

Approach

7. Using Gaps Between Dates

Here we will format rows depending on the gaps between the sales date and delivery date of the products.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly go to the Method 7 worksheet and select cell F6.

Formatting based on gaps to Do Conditional Formatting Highlight Row Based on Date

  • Then enter following formula.
=D6-C6

Formatting depending upon gaps to Do Conditional Formatting Highlight Row Based on Date

  • Click the Enter key.

  • Now select cell F6 and drag Fill Handle icon from F6 to F12.

  • Now we want to highlight the rows where gaps between the sales dates and Delivery date is higher than 5 days. For that select the data range.

  • Then again follow the steps from Method 2 and then enter following formula.
=$F6>5

  • Then we will get following result.

And we can see all the rows having gaps more than 5 days were highlighted here.

📕 Read More: Conditional Formatting Based on Date Range in Excel

Approach

 8. Based on Empty Dates

Here we will highlight those rows where the dates are not present in the cells.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Method 8 worksheet and select the data range.

Formatting based on empty cells to Do Conditional Formatting Highlight Row Based on Date

  • Then again we will follow the steps from Method 2 and enter following formula. After that click OK.
=$C6=””

  • And we will obtain results like below.

We can see that here we only highlighted the rows those don’t contain dates.

Approach

 9. Based on Non-Empty Dates

In this method, we will highlight the rows based on the non-empty dates.

⬇️⬇️ STEPS ⬇️⬇️

  • Select the Method 9 worksheet and select the table.

Formatting based on non-empty cells to Do Conditional Formatting Highlight Row Based on Date

  • Follow the steps from Method 2. Then enter given formula and click OK.

=$C6<>””

  • We will obtain following result after that.

We can see here that we have highlighted the rows only containing the dates.

Approach

10. Using WEEKDAY Function

In this method, we will highlight the dates which are Saturdays and Sundays. In this instance, we shall utilize WEEKDAY function.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Method 10 worksheet and select the data range.

Using Conditional Formatting

  • Follow Method 2‘s instructions. Then, input following formula and click OK. 1 represents Monday and 6 in this formula, whereas 7 represents Saturday, Sunday. So value higher than 5 will signify weekends.
=WEEKDAY($C6,2)>5

  • We will get following result.

Here the highlighted dates represent weekends.

Approach

11. Applying MATCH Function

In this method, we are going to highlight special dates. Here we will use the MATCH function.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to Method 11 Here in this dataset we can see that we have created another table mentioning Christmas on 25th December and we want to see whether any product was sold in this date and if any then we want to highlight that row.

Using Conditional Formatting Highlight Special Dates

  • Follow the steps of Method 2 and enter the following formula then click OK.
=MATCH($C6,$G$6,0)

Utilizing Conditional Formatting Highlight Special Dates

  • And we will see the result after that.

Here we can see that we have highlighted that row that contains the Christmas date.

📝  Takeaways from This Article

📌  In this article we illustrated 11 ways to excel conditional formatting highlight row based on date.

📌  We have used different criteria in this article to highlight our desired rows.

📌  We got to know about different functions such as AND, OR, IF, WEEKDAY, TODAY, MATCH, etc.

Conclusion

Finally, we have come to the end of this article. In this post, we have attempted to cover every method for excel conditional formatting row highlighting depending on date. As several approaches have been outlined in this article, you may easily choose which one is appropriate. If you have any questions, please leave them in the comments. Finally, we propose that you visit Excelden to discover more about Excel.

Related Articles

(Visited 72 times, 1 visits today)

Tags:

Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

Related Articles
We will be happy to hear your thoughts

Leave a reply

About Excelden
ExcelDen
Logo