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 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.

excel conditional formatting date less than 6 months from today

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.

Add new conditional formatting 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

excel conditional formatting formula and color


πŸ“Œ Step 3: Observe Results

  • After that, you will see the following result.

result after applying cond. formatting

  • You can also use the following formula to get the same result.
=EDATE(TODAY(),-6)<C5

Read More: How to Find If Date Is Within 3 Months in Excel (5 Easy Ways)


Similar Readings


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

excel conditional formatting for dates oler than 1 year

  • 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.

dates older than 1 year

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.

list of dates

  • Then you can use the following formula in the conditional formatting rule to do that on range C5:C12.
=B5-C5>10

excel conditional formatting for dates past due

  • After that, you will see the following result.

dates past due 10 days

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.

customer entry and exit times

  • Then use the following formula in the formatting rule for range B5:D12.
=D5-C5>TIME(1,0,0)

conditional formatting for elapsed time

  • After that, you will see the following result.

time elapsed more than 1 hour

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.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo