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 .
📁 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.
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.
⬇️⬇️ STEPS ⬇️⬇️
- Select data range.
- Then go to the Home Tab and click the Conditional Formatting and click on the Highlight Cells.
- 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.
- 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
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.
- Then Select Conditional Formatting in the Home Tab. After that, select New Rule.
- Then click on Use a formula to determine which cells to format.
- 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.
- Enter the following formula and click OK.
- And you will obtain following result.
Here we can see that we have highlighted the products sold in the shop on same date.
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.
- 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.
- 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
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.
- Then again follow the steps you followed in Method 2. Use the following formula and click OK.
- 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.
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.
- 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.
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.
- 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.
- Press the Enter key.
- Now select cell E6 and drag Fill Handle icon from E6 to E12.
- 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.
- Then press OK and you will the following results.
Here you can see that we only highlighted the rows which are true.
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.
- Then enter following formula.
- 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.
- 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
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.
- Then again we will follow the steps from Method 2 and enter following formula. After that click OK.
- And we will obtain results like below.
We can see that here we only highlighted the rows those don’t contain dates.
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.
- 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.
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.
- 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.
- We will get following result.
Here the highlighted dates represent weekends.
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.
- Follow the steps of Method 2 and enter the following formula then click OK.
- 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
- 5 Easy Ways to Highlight Highest Value in Excel
- Excel VBA: If Font Color Is Red Then Return Specific Output
- How to Apply Excel Formula to Change Text Color Based on Value
- 4 Ways to Use Conditional Formatting for Dates Overdue in Excel