Colouring Cells using a formula.

Etienne Desbiens · 07/07/16

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

Kevin McCartan · 07/07/16

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.

J. Craig Williams · 07/07/16

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

Etienne Desbiens · 07/07/16

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?)

Kennedy Stomps · 07/11/16

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

WorkisWork · 10/11/18

On another note, high jacking here, lol

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

Andrée Starå · 11/02/18

Hi,

Unfortunately, it's not possible as far as I know.

Please submit an Enhancement Request when you have a moment.

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Welcome to the Smartsheet Forum Archives

Colouring Cells using a formula.

Comments

Categories