22

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

Comments

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

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.

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

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

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

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

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

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

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

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.

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

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

 

Thanks

Kevin

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

 

 

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

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

Here :
=IF(((NETWORKDAYS(TODAY(), Finish6) / NETWORKDAYS(Start6, Finish6)) * 100) < 10, "Yellow", IF(AND(TODAY() > Start8, [% Complete]8 = 0), "Red", IF(AND(TODAY() > Finish8, [% Complete]8 < 1), "Red", IF(AND(TODAY() > Start8, [% Complete]8 < 1), "Green", IF(AND(TODAY() > Start8, [% Complete]8 = 1), "Green", IF(AND(TODAY() < Start8, [% Complete]8 = 0), "Gray", IF(AND(TODAY() < Start8, [% Complete]8 > 0), "Green")))))))
There was one extra ")" (what is the word for that? I don't know it in english).

But, currently, you use Red, Blue, Green, Gray and Yellow... There is a maximum of 4 colors (Red, Green, Blue, Yellow or Red, Green, Yellow, Gray)... So, currently, some result might just write "Gray" for example. I suggest removing "Gray" and replacing it by "". This way, instead of the color gray, nothing will appear.

 

Hope it helps!

Étienne Desbiens

Yep that makes sense, The english word for ( is paranthesis  :) .. 

 

You know the formula you gave me for % between start & finish to show yellow:

 

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

 

i have two issues with it, 1) if the task duration is 3 days and there is 2 days to go it doesnt work,

Start: 06/07/16 Finish: 08/07/16 --- today = 07/07/16

2 days divided by 3 days = 0.66 multiplied by 100 = 66 -- 

this is greater than 30 so will show green even though it is close to end date.

hope that makes sense.

 

so i guess i need it to work by percentage and finish - 5 days or something like that?

 

2) - if the duration of task is 1 day, i will get #Divide by zero error message.

 

Thanks 

Kevin.

 

 

Quite impressive Etienne.

I like the visualization.

 

For those that are having problems with speed due to complex formulas, it may help to optimize the formula by most likely to happen rather than most easy to code.

Visualization like this will help make the formula less difficult when doing that.


Craig

Hum... Lets see what you did :

 

if the task duration is 3 days and there is 2 days to go it doesnt work,

Start: 06/07/16 Finish: 08/07/16 --- today = 07/07/16

2 days divided by 3 days = 0.66 multiplied by 100 = 66 -- 

this is greater than 30 so will show green even though it is close to end date.

 

The goal of this formula is to show yellow if less than 30% of the allowed time is left.

On this example, you have one day in the past and two days left. Which means you have 66% of the time left. Technically, it is ok for it to show green.

 

You could add a condition that changes the color on yellow whenever the time left is equal or smaller to two days.

 

For the error divide, you can simply add a case where if the time allowed is less than two days, you automatically show the task yellow/green...

 

Hope that helps!

Étienne Desbiens

 

(I'm not proud of me... "The english word for ( is paranthesis  :) ." This is almost the same word in french (parenthèse). Why was I thinking about comma?)

Etienne-- That's a great formula breakdown, I'm going to save it to help better illustrate how formulas "run" :) 

On another note, high jacking here, lol

How do you write a formula to insert a .jpeg in a cell?