Help with conditonal formatting

Solved/Closed
croppman
Posts
4
Registration date
Wednesday February 2, 2011
Status
Member
Last seen
March 1, 2011
- Feb 2, 2011 at 03:57 AM
croppman
Posts
4
Registration date
Wednesday February 2, 2011
Status
Member
Last seen
March 1, 2011
- Feb 15, 2011 at 05:37 AM
Hello,

On our work tracker we have a 10 business day turnaround to get work completed.

Currently we have a formula that works out how long it has taken to complete a task and using conditional formatting this displays the cell in black if it goes above 5 business days.

I have used the 3 conditional formatting conditions to do the following: -

- Cell Value - between 4 and 5 - Display green colour

- Cell Value - equal to 3 - Display Amber

- Cell Value - less than or equal to 2 - Display Red

I've input this formula into the column to work out whether the task is going to expire or not.

=IF(I4="","", IF(NETWORKDAYS(TODAY(),WORKDAY(E4,10)) <0, "Out of KPI",NETWORKDAYS( TODAY(),WORKDAY(E4,10))-1) )

- E4 is the received date (the date from where the 5 days begins)

- I4 is the complete date

I want the countdown to disappear when the task is complete but using "IF(I4="","".....before the 2nd IF statement it should technically make the cell for the countdwon disappear. However it is still displayed when a date is entered but disappears when I4 is blank!

What am I doing wrong?

I have a test file which I can send if its easier. Please can anyone help?

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Feb 3, 2011 at 09:22 AM
Hi Croppman,

quote
However it is still displayed when a date is entered but disappears when I4 is blank!
unquote

Off course it is, look at your formula: IF(I4="",""
Meaning: When I4 is blank, then result must be blank.

Try replacing the "=" by "<>".
Meaning: When I4 is not blank, then result must be blank.

Does this yield the desired result?

Best regards,
Trowa
0
croppman
Posts
4
Registration date
Wednesday February 2, 2011
Status
Member
Last seen
March 1, 2011

Feb 15, 2011 at 05:37 AM
Hi Trowa

This is brilliant. Thank you, it makes the text disappear which now helps emmensely.

Thanks again

Croppman
0