Creating a Formula for Dates and Traffic Lights
Hi, all!
Our office is new to the SmartSheet community. I am having some trouble creating a formula for a sheet. I have the following columns:
- "Initial Contact" - formatted as a date
- "30 Days Since Contact" - formatted as a green traffic light
- "60 Days Since Contact" - formatted as a yellow traffic light
- "90 Days Since Contact" - formatted as a red traffic light
This is what I am trying to set up:
- If today's date is 30 days greater than the initial contact date, the formula should create a green traffic light.
- If today's date is 60 days greater than the initial contact date, the formula should create a yellow traffic light.
- If today's date is 90 days greater than the initial contact date, the formula should create a red traffic light.
I am having trouble combining the automated function for today's date, the formula for "X" days past today's date, and the formula for the traffic lights. I've been checking around the community and can't find an answer. Does anyone have suggestions? I included a screenshot of my sheet for reference.
Thank you!
-Julianne
Comments
-
Paul Newcome ✭✭✭✭✭✭Are you wanting to have each one it's own column? If so you would use something along the lines of this...
=IF(AND(ISDATE([Initial Contact]@row), TODAY() > [Initial Contact]@row+ 30), "Green")
=IF(AND(ISDATE([Initial Contact]@row), TODAY() > [Initial Contact]@row+ 60), "Yellow")
=IF(AND(ISDATE([Initial Contact]@row), TODAY() > [Initial Contact]@row+ 90), "Red")
.
If you wanted to have it all in one single Status column, you would use something like this...
=IF(ISDATE([Initial Contact]@row), IF(TODAY() > [Initial Contact]@row+ 30, "Green", IF(TODAY() > [Initial Contact]@row+ 60, "Yellow", "Red")))
thinkspi.com
-
Julianne ✭
Thank you, Paul! That worked perfectly!
-
Paul Newcome ✭✭✭✭✭✭
-
Ess ✭✭✭
Hi Paul I have a sheet that hasWorkstream columnunder each workstream I have differentoutputsand under those outputs I have differentactivities.
They all have %progress, Status and end date columns.
I have 6 workstreams. I am trying to find a formula that can help me create a simple dashboard to show progress of each workstream.
I want to use traffic light column with %Progress column and end date.
-
Paul Newcome ✭✭✭✭✭✭
@EssAre you able to provide a mocked up screenshot with manually entered data that shows what you are trying to accomplish?
thinkspi.com
-
Ess ✭✭✭
@Paul NewcomeYes in that screen shot I have 5 workstreams as you can see.. I want to create a heath status column to show me the health status of each workstream which has different tasks/activities. I dont know how to go about it. I want create a heath status column with reference to end date 31/12/2020 of the activities. To see which workstream is doing better and which one is lagging behind.
-
Ess ✭✭✭
@Paul Newcome我有点confused. Paul I want to calculate the health status of my tasks looking at the end date. Lets say please complete for me the formula plus End Date being less than 2020.12,31
=IF([email protected]= ''Complete'', "Green", IF([email protected]=''In Progress'', "Yellow", IF([email protected]= ''Not Started'', "Red")))
-
Ess ✭✭✭
Hi Paul. I figured answer for my previous question.
Now I need a formula kindly. I have my heath column with Green,Red,Blue.
Green=Okay
Red=Delayed
Blue=Complete
I want the parent row to roll up for me the average heath status of the children rows based on this:
If Red>Green and Blue =Red
If Green>Blue =Green
If Blue>Red and Green=Green
If all Blue=Blue
all Green=Green
-
Ess ✭✭✭
All that based on End Date of 2020,12,31
-
Paul Newcome ✭✭✭✭✭✭
@EssYou can try something like this...
=IF(OR(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), COUNTIFS(CHILDREN(), "Green")> COUNTIFS(CHILDREN(), "Blue"), COUNTIFS(CHILDREN(), "Blue")> COUNTIFS(CHILDREN(), "Red")), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", "Red"))
thinkspi.com
-
Ess ✭✭✭
@Paul NewcomeThanks Paul the formula is working however its not giving me the results I want.
Maybe we can make it simpler.
Any childrenwith Red,parent = Green
No red,parent =Yellow
Help Article Resources
Categories
Check out theFormula Handbook template!
Hi @John Littler<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":107942,"type":"question","name":"How do I return DISTINCT values from another sheet based on specific dates within that sheet?","excerpt":"Hello, I have a need to return a Distinct Item ID from another sheet based on a date range from another date column within that same external sheet. The formula below works; however, once I run out of Distinct item IDs that meet my date criteria it continues to return results that fall outside that date range. Current…","snippet":"Hello, I have a need to return a Distinct Item ID from another sheet based on a date range from another date column within that same external sheet. The formula below works;…","categoryID":322,"dateInserted":"2023-07-22T00:19:14+00:00","dateUpdated":null,"dateLastComment":"2023-07-24T14:28:01+00:00","insertUserID":123219,"insertUser":{"userID":123219,"name":"Matt Foss","url":"https:\/\/community.smartsheet.com\/profile\/Matt%20Foss","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bCFRG7SgS_o!w6Nev-KRhHQ!jkKTibQm-FP","dateLastActive":"2023-07-24T14:20:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-24T17:50:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":33,"score":null,"hot":3380195835,"url":"https:\/\/community.smartsheet.com\/discussion\/107942\/how-do-i-return-distinct-values-from-another-sheet-based-on-specific-dates-within-that-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107942\/how-do-i-return-distinct-values-from-another-sheet-based-on-specific-dates-within-that-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107942,"commentID":386638,"name":"Re: How do I return DISTINCT values from another sheet based on specific dates within that sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386638#Comment_386638","dateInserted":"2023-07-24T14:28:01+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-24T17:50:30+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\/74OMVUEY31TO\/example.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Example.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-24T14:20:29+00:00","dateAnswered":"2023-07-22T02:23:22+00:00","acceptedAnswers":[{"commentID":386531,"body":" I think this is what you are trying to accomplish. I assume you are dragging the formula down, as this will not work as a column formula due to the absolute references. There were a few issues with the formula you posted, maybe there were some typos when transferring it to your post? <\/p> =IFERROR(INDEX(DISTINCT(COLLECT({Item ID}, {Create Date}, >= $[Start Date]$1, {Create Date}, <= $[End Date]$1)), [Row ID]@row), \"//m.santa-greenland.com/community/discussion/53296/\")<\/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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
=IF(OR(Idea34 = \"//m.santa-greenland.com/community/discussion/53296/\", Idea58 = \"//m.santa-greenland.com/community/discussion/53296/\"), \"//m.santa-greenland.com/community/discussion/53296/\", IF(OR(Idea34 < 0, Idea58 < 0), \"Red\", \"Green\"))\n<\/pre>
