Colouring Cells using a formula.

Kevin McCartan

Hi,

 

I am trying to colour cells depending on the data in cells to show a status. below is the statement so far but cant get it to work correctly. any help would be great.

 

=IF(AND(TODAY() > Start166, [% Complete]166 = 0), "RED", IF(AND(TODAY() > Finish166, [% Complete]166 > 100), "RED", IF(TODAY() < Start166, "WHITE", IF(AND(TODAY() > Start166, [% Complete]166 = 100), "GREEN"))))

 

Thanks in advance

Kevin

Etienne Desbiens

Hi,

 

First of all, do you want to color the cell or to show a colored symbol?
If you want to color it, you need to use conditionnal formatting.
For symbols, here is what I found :

=IF(AND(TODAY() > Start9, [% Complete]9 = 0), "Red", IF(AND(TODAY() > Finish9, [% Complete]9 > 100), "Red", IF(TODAY() < Start9, "Gray", IF(AND(TODAY() > Start9, [% Complete]9 = 100), "Green"))))
First of all, you wrote the colors in complete CAPS. They must have a capital letter at the beginning and the rest must me small letters.
Then, there is no white color in those. There is gray though. You could also write "" so that, in a certain case, nothing appears.

Do you have any error left? Is it doing the right thing? In the case where it still doesn't work, can you provide me the different conditions you want to apply?

 

Hope it helps!

 

Étienne Desbiens

Kevin McCartan

Hi,

 

Thanks for your response, I have inputted your formula and i am getting no errors but only the first part of the formula is showing a result? and the result is showing the word Red. what i am trying to show is the colours or Symbol so i can quickly see at a glance what their status is. i started with conditional formatting which worked but only for say the % completed column, couldnt see how to work it when the formula got a little more complicated with multiple cells in play. 

 

currently i have a standard account, will that affect the way the formula works?

 

Thanks

Kevin.

Etienne Desbiens

Hi,

Your account doesn't change anything to that... A part of the problem here is that your result collumn is probably in text type. You need to put the Symbol type and to select the red, green, yellow and gray one.

 

Start trying that to see if it works...
Could you tell me the conditions you want to use?

 

Hope that helps,

Étienne Desbiens

Etienne Desbiens

I tried to interpret your code... Here is what I made :

 

=IF(AND(TODAY() > Start5, [% Complete]5 = 0), "Yellow", IF(AND(TODAY() > Finish5, [% Complete]5 < 100), "Red", IF(TODAY() < Start5, "Gray", "Green")))

 

Here are the conditions I used :

If the Start date is in the future, Gray

If the Start date is in the past and that the completion is at 0%, Yellow

If the End date is in the past and that the completion is less than 100%, Red

If Today is between the start date and the end date and that completions is not 100%, Green

 

Is that what you wanted?

Étienne Desbiens

Kevin McCartan

Yeah thats it, I will test again in the morning once i have the Column changed to symbol, thanks again for your help, i will let you know how i get on.

 

Thanks,

Kevin

Kevin McCartan

Hi,

 

Just tested this, and it works, i just had to change 100 to 1 for it to show Green. 

The Only one that doesnt work now is:

 IF(AND(TODAY() > Finish5, [% Complete]5 < 1), "Red"

 

it just shows a blank cell?

 

Any ideas?

 

Thanks

Kevin

Etienne Desbiens

That is because you don't have two results at the end of your conditions...
Here is the logic behind it :

IF the conditions stated are true, show the first answer ("Red")

else (the conditions are false), show the last answer (none here).

 

You simply need to add a comma and another color.

 

(The reason why you changed 100 to 1 is that you probably use the percentage option on your data)

 

Hope that helps!

Étienne Desbiens

Kevin McCartan

Yep, Thanks for that i got it working with your help. 

 

is there a formula so that i can calculate the days between start and end dates and then calculate it to show Yellow if it is nearing the end date?

 

Thanks,

Kevin

Etienne Desbiens

No problem!

 

To calculate the days, here are the options :

Netdays(Startdate, enddate) gives you the days between two dates.

Networkdays(Startdate, Enddate) gives you the work days between two dates.

You could have something like : if(netdays(Startdate, enddate)<5,"Yellow"...

 

Hope that helps!

Étienne Desbiens

Kevin McCartan

Thanks for that, i was thinking of doing something similar to what you have suggested,

but what if the netdays was 3 days and another 200 days, it would always show yellow for the 3 days one, is it possible to say take a third of the netdays and only be yellow for them .. if that makes sense,

 

Kevin.

Etienne Desbiens

That totally makes sense!
You simply have to find out what portion of the allowed period is left:

=if(((netdays(Today(), DueDate)/netdays(StartDate,DueDate))*100)<30,"Yellow", "Green")

This calculates if ((DueDate-Today())/(DueDate-StartDate))*100 is smaller then 30%

 

Hope that helps!

 

Étienne Desbiens

Kevin McCartan

Thanks, that worked perfect. How do the formulas take priority? if two formulas are correct which result is shown?

 

Thanks

Kevin

J. Craig Williams

It isn't two formulas really, it is one.

IF statements are done left to right. 

 

IF (statement 1 is true)

do this

ELSE (statement 1 is not true)

do that

 

when the "do this" includes an IF statement, but statement 1 is false, the system will never bother with checking that secton IF statement.

And if statement 1 is true, it doesn't matter what the "do that" does - it could even produce an error but the system won't know it until it needs to check.

 

Craig

 

 

Kevin McCartan

Thanks Craig, i am getting an error message if i add this formula to the front of my formulas so that it checks this first, IF(((NETWORKDAYS(TODAY(), Finish265) / NETWORKDAYS(Start265, Finish265)) * 100) < 10, "Yellow")

 

but if i run it on its own it works fine?

 

the error is #MISSING OR INVALID PARAMETERS

 

altogether it is this:

=IF(((NETWORKDAYS(TODAY(), Finish250) / NETWORKDAYS(Start250, Finish250)) * 100) < 15, "Yellow"), IF(AND(TODAY() > Start250, [% Complete]250 = 0), "Red", IF(AND(TODAY() > Finish250, [% Complete]250 < 1), "Red", IF(AND(TODAY() > Start250, [% Complete]250 < 1), "Green", IF(AND(TODAY() > Start250, [% Complete]250 = 1), "Green", IF(AND(TODAY() < Start250, [% Complete]250 = 0), "Gray", IF(AND(TODAY() < Start250, [% Complete]250 > 0), "Green"))))))

 

Thanks

Kevin

Etienne Desbiens

Here is an example to continue  on what Craig explained...

This is an explanation I made to help my coworkers understand some formulas.

First of all, here is the formula :

 

IF([% Complete]1 = 1, "Blue", IF(AND(NETDAYS([Plan End Date]1, TODAY()) > 1, AND(NOT(ISBLANK([Action Plan]1)), NOT(ISBLANK([Recovery Date]1)))), "Yellow", IF(NETDAYS([Plan End Date]1, TODAY()) > 1, "Red", "Green")))

 

It's not short at all and it can be confusing due to the multiple IF and AND.

 

So, here is the text explanation I made for them :

If the completion is equal to 1 (which is 100%) : Show the color blue.
Else, if the due date is in the past and that there are no action plan (Neither a date, neither actions) : Show the color yellow.

Else, if the due date is in the past : Show the color red.

Else : Show the color green.

 

And here is my small scheme to help visualise it. Some people prefer seeing it this way.

Hope it helps!

Étienne Desbiens