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
-
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")))
-
Thank you, Paul! That worked perfectly!
-
-
Hi Paul I have a sheet that has Workstream column under each workstream I have different outputs and under those outputs I have different activities.
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.
-
@Ess Are you able to provide a mocked up screenshot with manually entered data that shows what you are trying to accomplish?
-
@Paul Newcome Yes 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.
-
@Paul Newcome I am abit 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(Status@row = ''Complete'', "Green", IF(Status@row =''In Progress'', "Yellow", IF(Status@row = ''Not Started'', "Red")))
-
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
-
All that based on End Date of 2020,12,31
-
@Ess You 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"))
-
@Paul Newcome Thanks Paul the formula is working however its not giving me the results I want.
Maybe we can make it simpler.
Any children with Red, parent = Green
No red, parent =Yellow
Help Article Resources
Categories
Check out the Formula Handbook template!
