Conditional Formatting for Due Dates

[Jess709]

I'm attempting to use conditional formatting to change the color of a cell (A2) when it meets certain criteria (see below). I've tried many combinations of formulas and can't seem to get it 100% correct. I'm using Excel 2010 and I've attached a sample file.
________________________________________
If the date in A2 is equal to or greater than today's date, format to the color GREEN

If the date in A2 is within the past 13 days, format to the color YELLOW

If the date in A2 is within the past 14 days or more, format to the color RED
___________________________________

After I've accomplished this I will need to add another component in regards to whether the project is on budget or not. I thought I would start with this part of the formula first.

Thank you for any assistance you can provide.

 

[eibi]

Jess,

Welcome to the forum.

Take a look at the attached file; does it do what you want?

I noticed that you are putting an IF() into your conditional formatting rule, which isn't really necessary, because the conditional formatting is by definition applied only when the formula you provide results to TRUE.

The formula you need to apply green, for example, is simply:

= A2>=TODAY()

All best.

 

[Tiago MF]

Eibi, just note that for your conditional formatting to work the way the OP wants you need to switch the order each rule is applied to the other way around, i.e. Green should be first, then Yellow and then Red.

Also, if it's to turn yellow within the past 13 days, I believe you have to subtract by 12 not 13. To apply on the Red rule as well if so.

 

[Jess709]

Tiago - I was actually just getting ready to reply that it wasn't working correctly, but now that I changed the order of the rules it is working great. I also changed the "yellow' rule to -12.

Thank you to both who replied. I'll post the next parameter shortly which will refer to whether the project is within a budget as well.

 

[Jess709]

Ok, now I'm ready for the 2nd condition of the formatting. Below are the 3 parameters that I'm working with. I have the component of the dates already in the conditional formatting, which is what you both helped me with earlier. Now I need to factor in whether the project is within budget. I've added the 2 necessary columns to the attached file of SOW Estimate and Formal Estimate.

I need the conditional formatting to check both the due date compared to TODAY and if the "formal estimate" is equal, above, or below the "sow estimate." The three colors are below. Green is on time, Yellow is Over budget but by less than 10%, and red is over budget by more than 10%.

The formatting needs to distinguish if either of the types of conditions are met. For instance if the project is on time but over budget by 5%, it would turn yellow.


On-Time On-Budget (GREEN)


Over Budget but less than 10% or Late < 2 weeks (YELLOW)


> 10% Over Budget or > 2 Weeks late (RED)

Thank you again in advance!

 

[Tiago MF]

Hi Jess

Can you please validate the following file?

I've added a row for each possible condition so it's easier for you to confirm.

Best,

 

[Jess709]

Hi Jess

Can you please validate the following file?

I've added a row for each possible condition so it's easier for you to confirm.

Best,

I've tested the file and the yellow and red parameters aren't working correctly. For instance row 9 has a date of 4/22/15 and % budget of 5%. The date should be turning RED b/c of the date being more than 13 days older than today, 5/12/15. I'll play around with it and try and figure it out. If you come up with a solution let me know.

Thanks!

 

[Jess709]

I think I got it fixed. I moved the RED conditions before the YELLOW conditions, but I can't remember if they were already set up that way or not. So at the end it is working, but I'm not sure what I may have done to get it working

Thanks!

 

[eibi]

Jess,

If you look at the fix you proposed in the previous post, and change cell D9 to 12%, that row stays yellow (but I think you want it to go to red, right?)

My concern is that you may be creating overlapping formatting rules where more than one can be true at the same time -- Luke M taught me about this in this in a previous thread: http://chandoo.org/forum/threads/convert-color-to-number.22600/#post-136572

Quoted from Luke M in that thread:

It's bad practice to have ambiguous rules, and the actual color ends up being decided by what order you decided to write the rules...See the attached for an example of why overlap doesn't really work well.​

Helpful?

 

[Tiago MF]

Ah, you were scaring me now! I just opened the file and it looked OK to me. I guess you switched the order of the rules of the cond. form.

Just to clarify, yes, the RED one is supposed to be on the middle this time, like I have on the file I sent you. I guess I could have mentioned that so you didn't got confused

Anyway, I'm glad you got it working

 

[Jess709]

Jess,

If you look at the fix you proposed in the previous post, and change cell D9 to 12%, that row stays yellow (but I think you want it to go to red, right?)

My concern is that you may be creating overlapping formatting rules where more than one can be true at the same time -- Luke M taught me about this in this in a previous thread: http://chandoo.org/forum/threads/convert-color-to-number.22600/#post-136572

Quoted from Luke M in that thread:

It's bad practice to have ambiguous rules, and the actual color ends up being decided by what order you decided to write the rules...See the attached for an example of why overlap doesn't really work well.​

Helpful?

Eibi - I do understand your point. I went into my test data and changed D9 to 12% and it does turn the date to red.

Are you thinking that if I select the "stop if true" option under the GREEN conditions it would fix any potential overlap?

-Jess