Creating a Formula for Dates and Traffic Lights

Julianne

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

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

Julianne

Thank you, Paul!  That worked perfectly!

Paul Newcome

Happy to help! 

Ess

@Paul Newcome

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.

Paul Newcome

@Ess Are you able to provide a mocked up screenshot with manually entered data that shows what you are trying to accomplish?

Ess

@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.

Ess

@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")))

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

@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"))

Ess

@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