Upcoming Due Dates Conditional Formating

[JJgold]

I have created a trademark tracking spreadsheet (which I was trying to attach but cant quite figure out how to do). I am stuck on highlighting cells when a due date is approaching.

For example, I have a formula in cell M1 that returns a due date of 6 months from the date in cell L1. Now I want to conditionally format cell M1 to turn Blue when the due date (M1) is between 4-3 months from today, Orange when it is 3-2 months to the deadline yellow when it is 2-1 month from the deadline and red when it is 1 month - the actual due date.

I also need help with a very similar situation. I have a formula in W1 that returns the date 5 years from the date in L1 and a formula in X1 that returns the date 5 years 12 months and 30 days from the date in L1 (basically a document must be filed between the 5th and 6th year of the registration date [Cell L1]. I want to conditionally format Cell A1 different colors based on how far out the due date is. For example, A1 turn green from the start of the 5 years it will turn and stay green for 3 months. then I want A1 to turn blue when the due date is 9 months out from today (and stay blue) then turn orange when 6 months out from today and stay orange until 3 months from today in which case it will turn yellow and stay yellow until 1 month before the deadline and the red when today is 30 days- the actual due date.

I have a sample to upload if someone can point out how to do so.

Thank you for taking the time to help me out.

 

[AAAROD]

Here is a basic idea on conditional formatting formulas to accomplish this:

BLUE =IF(($M$1-TODAY()>90)*($M$1-TODAY()<121),1,0)
ORANGE =IF(($M$1-TODAY()>60)*($M$1-TODAY()<91),1,0)
YELLOW =IF(($M$1-TODAY()>30)*($M$1-TODAY()<61),1,0)
RED =IF(($M$1-TODAY()<=30),1,0)

These formulas aren't completely accurate on a strict month basis in their calculations, however hopefully this gives you a rough idea on how to tackle this. Perhaps someone here has a more elegant solution.

 

[JJgold]

Thank you. I will give it a shot. But what do you mean by not completely accurate on a strict month basis? Will it at least warn me before the due date? Also, what is the function of the *?

 

[AAAROD]

I just used a 30 days per month calculation so won't be spot on for actual months if that is what you want.

The asterisks are part of the Boolean style arguments I used. Basically if both statements on each side of the asterisk are true then the color change will happen, if false then they won't.

 

[JJgold]

I just used a 30 days per month calculation so won't be spot on for actual months if that is what you want.

The asterisks are part of the Boolean style arguments I used. Basically if both statements on each side of the asterisk are true then the color change will happen, if false then they won't.

Thank you for your help. Much appreciated!