Excel Conditional Formatting for Date Less Than 6 Months from Today

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 that are 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 do that.

How to Perform Conditional Formatting for Date Less Than 6 Months from Today in Excel: 3 Easy Steps

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.

📌 Step 3: Observe Results

• After that, you will see the following result.

• You can also use the following formula to get the same result.

Read More: How to Compare Dates in Two Columns in Excel

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.

• You can also use the following formula instead of the earlier one.

• After that, you will get the following result.

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.

• After that, you will see the following result.

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.

• After that, you will see the following result.

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.

Download Practice Workbook

You can download the practice workbook from the download button below.

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. Stay with us and keep learning.

<< Go Back to Dates | Compare | Learn Excel