How to set up conditional formatting for 30 days, 60 days and 1 year before expiry?

Question

Hi.

I am trying to set conditional formatting for two separate columns in Excel. Hopefully I explain this well enough.

The first is for vehicle tag expiration dates and I need to set the formatting for the following

• Turn yellow 60 days before expiration
• Turn red day of expiration
• Be green if expiration is 61+ days away

The second column is for vehicle service dates and based on the last day of service

• Turn red 1 year past last service date
• Turn yellow within 60 days of 1 year since last service date
• Be green is service was done between 61 days and 1 year.

Answer 1

Hi,

Let me help you with it

I comment you in detail for the first one of “Fill in yellow for those who are 60 days close to expiration”. The others are very similar cases, you would apply the same logic.

1. First, you select the entire column to which you want to apply conditional formatting. In the home tab look for Conditional Formatting and follow the image below.
2. Select the last option “Use a formula...”.
3. The formula to use is

      =TODAY()-$E2<=60
   

   Explanation of the formula: The TODAY() function will dynamically return the date of the pc. That is, it will vary each day you see it in your excel. If you open today the TODAY() function will return a date, if you open it tomorrow, it will return another date. This dynamic date you subtract the value of what your due date column indicates and if this difference is greater than 60; if so, it will fill the color that is indicated according to image. In this way, you can add conditional formats as needed

I share all conditional formats

I hope I have helped you, but I remain attentive to any questions or comments.

---

If it helped you, please mark it as an answer so that other users will know that the answer is here in case they have the same question.

Answer 2

Hi @HI34

Thank you for reaching out to Microsoft Q&A. 

It looks like you're setting up conditional formatting for vehicle tag expiration and service dates — I gave it a test on my end in Excel, and everything worked well. Hope this helps and makes things easier for you! 

How to apply conditional formatting: 

1. Select your target cells. 
2. Go to Home > Conditional Formatting > New Rule. 
3. Choose “Use a formula to determine which cells to format.” 
4. Enter the relevant formula. 
5. Click Format, select the color you want (Red, Yellow, or Green), then click OK. 
6. Repeat the steps for each condition. 

For Vehicle Tag Expiration Dates (Column A): 

• Red: Already expired or expiring today =A2<=TODAY() 
• Yellow: Will expire within the next 60 days =AND(A2>TODAY (), A2-TODAY()<=60) 
• Green: More than 60 days until expiration =A2-TODAY()>60 

For Vehicle Service Dates (Column B): 

• Red: Overdue (more than 1 year since last service)  =TODAY()>EDATE(B2,12) 
• Yellow: Within 60 days before the 1-year mark  =AND(TODAY()>=EDATE(B2,12)-60, TODAY()<=EDATE(B2,12)) 
• Green: Service was done between 61 days and 1 year ago  =AND(TODAY()>=B2+60, TODAY()<=EDATE(B2,12)-61) 

If you need further assistance, please let us know. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.