Md. Shamim Reza
This article highlights how to use conditional formatting for a date less than 6 months from today in excel. Assume you work in an export company. You need to warn the production unit whenever the delivery date gets to less than 6 months from the current day. Or you may need to remind the management about the manufactured products still not exported and have less than 6 months until the expiry date. How will you do it? Well, you can easily do that using conditional formatting in excel saving a lot of time and effort. Follow the article to learn how to o that.
Download Practice Workbook
You can download the practice workbook from the download button below.
Steps to Perform Conditional Formatting for Date Less Than 6 Months from Today in Excel
Assume you have the manufacturing dates of some products. Now you want to apply conditional formatting to highlight the dates less than 6 months from today in excel.
Follow the steps below to be able to do that.
📌 Step 1: Select Range & Add New Rule
- First, select the dates (C5:C12) and then go to Home >> Conditional Formatting >> New Rule.
📌 Step 2: Choose Rule Type, Enter Formula & Select Fill Color
- Next, choose to Use a formula to determine which cells to format as the Rule Type. Then enter the following formula in the formula box. Now click on Format, pick a Fill color, click OK, then Apply, and then OK again.
=DATEDIF(C5,TODAY(),"m")<6
📌 Step 3: Observe Results
- After that, you will see the following result.
- You can also use the following formula to get the same result.
=EDATE(TODAY(),-6)<C5Read More: How to Find If Date Is Within 3 Months in Excel (5 Easy Ways)
Similar Readings
- How to Compare If Date Is Before Another Date in Excel
- Conditional Formatting for Dates Older Than Certain Date in Excel
- How to Use COUNTIF for Date Greater Than 30 Days in Excel
- Excel Conditional Formatting for Date Within 3 Months (3 Methods)
- How to Compare Dates in Two Columns in Excel (8 Methods)
More Cases of Conditional Formatting for Dates in Excel
Case 1: Conditional Formatting Dates Older Than 1 Year
Now assume you want to apply conditional formatting to dates older than 1 year.
- Then use the following formula for the formatting rule.
=DATEDIF(C5,TODAY(),"d")>365
- You can also use the following formula instead of the earlier one.
=DATEDIF(E5,TODAY(),"y")>=1- After that, you will get the following result.
Read More: Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)
Case 2: Conditional Formatting Based on Date Past Due
Now assume you have the following dates. You want to apply conditional formatting to highlight the dates past due 10 days.
- Then you can use the following formula in the conditional formatting rule to do that on range C5:C12.
=B5-C5>10
- After that, you will see the following result.
Read More: Excel Conditional Formatting Based on Past or Due Date
Case 3: Conditional Formatting Based on Elapsed Time
Assume you have a data entry worksheet where you record the entry and exit times of customers to a shopping mall. Now you want to apply conditional formatting to find out the customers who spend more than one hour in the mall.
- Then use the following formula in the formatting rule for range B5:D12.
=D5-C5>TIME(1,0,0)
- After that, you will see the following result.
Read More: Excel Formula If One Date is Greater Than Another Date
Things to Remember
- Don’t forget to select the desired range before applying conditional formatting. Otherwise, you won’t get the expected result.
- The TODAY function is a volatile function. It returns the current date and time every time you refresh the worksheet.
Conclusion
Now you know how to apply conditional formatting in excel for a date less than 6 months from today. You can customize the formulas as required (i.e use 3 for 3 months). Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
