Complex nested IF, AND with traffic light symbols
Hi All,
I'm working on automating a symbol highlighter to indicate project progress as well as alerting if key milestones are being compromised. The formula below is structured based on the following columns:
- Finish:The Activity completion date
- Start:The activity start date
- Date:A column containing fixed dates to which certain activities must be completed (these are blank for the most part, but key milestones will be entered into this column).
I'm using the symbols Red/Green/Yellow/Gray, and what I'm trying to achieve is:
- If the Finish date is in the past, set the symbol to Green (assume activity is complete)
- If TODAY() is between start and finish dates, set the symbol to Yellow (assume the activity is in progress)
- If the Start date is in the future, set the symbol to Gray (assume the activity is not started yet)
- Finally, if the Fixed date is less than the finish date (and not blank), set the symbol to Red (assume the milestone is compromised)
Steps 1-3 above works in the formula below, but I'm struggling to get the final step to work, and as I'm not too familiar with these formulas, I have not been able to get this to work from browsing the support and would appreciate any help to finalize this.
=IF(
Finish1 < TODAY(), "Green",
IF(AND(Start1 <= TODAY(), Finish1 >= TODAY()), "Yellow",
IF(Start1 > TODAY(), "Gray",
IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"
)))))
Thank you!
/Johnny
Comments
-
L_123 ✭✭✭✭✭✭
Original
1 =IF(
2 Finish1 < TODAY(), "Green",
3 IF(AND(Start1 <= TODAY(), Finish1 >= TODAY()), "Yellow",
4 IF(Start1 > TODAY(), "Gray",
5 IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"
)))))
First of all, the second AND statement (Line 3) is useless. You have a stacked if, and the Finish >= Today() part is already checked in the first statement.
1=IF(
2 Finish1 < TODAY(), "Green",
3 IF(Start1 <= TODAY(), "Yellow",
4 IF(Start1 > TODAY(), "Gray",
5 IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"
Next the criteria on line 4 is also redundant. We already filtered by <= today(), so another filter by >today() is redundant.
1=IF(
2 Finish1 < TODAY(), "Green",
3 IF(Start1 <= TODAY(), "Yellow",
4 "Gray"))
X. IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"
Now X is outside of our stacked if. But there seems to be several issues with the statement so lets take a look at it.
The second criteria of the and does not require an if statement, the if is implied. (side note, the if statement wouldn't work anyway as every if statement requires a return before you end it)
This leaves us with
IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red"
So back to our main statement
1=IF(
2 Finish1 < TODAY(), "Green",
3 IF(Start1 <= TODAY(), "Yellow",
4 "Gray"))
X. IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red"
The X is still outside our statement, but now it is correct theoretically. We are only using one of the criteria of the if statement, so why don't we move it up a little bit, and use "Gray" as the false of the statement.
1=IF(
2 Finish1 < TODAY(), "Green",
3 IF(Start1 <= TODAY(), "Yellow",
4 IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red","Gray"))
or in a format you can copy paste
=IF(Finish1 < TODAY(), "Green",IF(Start1 <= TODAY(), "Yellow", IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red","Gray" -
Mike Wilday ✭✭✭✭✭✭@LNice detailed walkthrough.
-
What an amazing walkthrough. Thank you so much!
One problem I just discovered is that the Red does not "override" the yellow, and the Red should be the most important state. Any suggestions on how I might go around ensuring the last statement is the "highest" priority setting?
Thanks again for all your help!
/Johnny
-
A few attempts with something like the following does not seem to parse, so not sure how to change the priorities of status:
=IF(AND(Date22 < Finish22, NOT(ISBLANK(Date22))), "Red", IF(Start22 <= TODAY(), "Yellow", IF(Finish22 < TODAY(), "Green", "Gray”)))
-
L_123 ✭✭✭✭✭✭
这是正确的想法,我不确定是什么wrong with yours, here is mine.
=IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red",IF(Finish1 < TODAY(), "Green", IF(Start1 <= TODAY(), "Yellow", "Gray")))
The concept when changing the priority of the stacked if statement is how early they come in the statement. so right now the priority of my statement is:
1. Red 2. Green 3. Yellow 4. Grey
Johnny's priority is
1. Red 2. Yellow 3. Green 4. Grey
Which is probably more likely what you would want. The easiest way to change the priority of a stacked if statement, especially a more straightforward one like this, is to post it into notepad and put each if statement on its own line. I've posted some pictures below of how I solved this problem.
-
L_123 ✭✭✭✭✭✭
Thanks. I feel like it is much easier when it's broken down into parts, for my own understanding anyway. This is how I normally problem solve these type of issues, just wrote out my process.
-
So, after a bit more research, it looks like I'd need to separate the highest prio ("Red" symbol) into an OR statement as this should overrule all the other ones regardless of their validations. I'm still not there, but here's where I'm at.
- IFthere is a hard date in Date22ANDit is less than Finish22,THENset the Symbol to Red.
- IFnone of the above are true,THENfollow the basic rules for Yellow, Green and Gray.
Any suggestions of why the below does not parse, and also guidance on if this would be the correct approach?
=IF(OR(AND(Date22 < Finish22, NOT(ISBLANK(Date22))), "Red"), IF(OR(Start22 <= TODAY(), "Yellow", IF(Finish22 < TODAY(), "Green", "Gray”))))
Thanks again for any help cracking this!
/Johnny
-
YASS! You cracked the code
This works a charm, thank you so much for your support, I'm super stoked to have this working and it will help me run things much easier.
Thanks again!
/Johnny
-
Ola123 ✭
Please I need help with this problem.
How do I use the traffic light symbols to indicate:
- If the due date is in the past and % complete is less than 100 then health is Red
- and if the due date is in the future and % complete is less than 100 then health is grey
- and if the due date is in the past and % complete is equal to 100, then health is green
- and if the due date is in the future and % complete is greater than 50% then health is yellow
Many thanks for your help with this.
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
