Health status formulas and automation
Hello anybody,
Please help me with automation formulas for the Health column? I'm very new to Smartsheet and struggling a bit! I'm trying to do the following:
Red flag - when the date has passed
Yellow fag - when the date has not passed
Thank you
Best Answer
-
Yes, this is definitely possible!
All you need to do is to say that if the date in the "1st Delivery Date" column is in the past, the column should show "Red". Otherwise (if the date is Today or in the Future), then show "Yellow".
Try this:
=IF([1st Delivery Date]@row < TODAY(), "Red", "Yellow")
Keep in mind that blank cells will be read as "in the past" which means that if the "1st Delivery Date" is blank it will be Red.
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
To do this you will need additional requirements (fields to be considered) than just the date coz date alone is not enough to decide when it is Yellow or Green (and Blue or Grey if you are using 4 lights).
-
Hi Titilope,
I have the same question. Can someone help us? What additional fields are needed and what would the formula be?
Thanks@
-
Can you tell us what additional requirement do we need?
-
Hi @Titilope Laniyan and @Bob Kernan
Are you able to post a screen capture of your sheet set up so we can see your columns/column types (but please block out any sensitive data), and can you define exactly when you want each colour to appear?
The way to build this formula is to add a number of IF statements together in a long "nested IF" formula, but we need to know what the formula is looking for, first.
For example, you're wanting the ball to turn red if a date in a date column is older than today, is that correct? That would be this IF statement:
=IF([Date Column]@row < TODAY(), "Red"
Then what colour should it be when that date IS today? Yellow?
IF([Date Column]@row = TODAY(), "Yellow"
And then if it's in the future, you could do Green:
IF([Date Column]@row > TODAY(), "Green"
Add them together:
=IF([Date Column]@row < TODAY(), "Red", IF([Date Column]@row = TODAY(), "Yellow", IF([Date Column]@row > TODAY(), "Green")))
Does that make sense?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Here is the screen capture of the sheet.
My goal is whenever the 1st delivery date column is updated. when the date passed- turn to red flag and Yellow flag - when the date has not passed.
I don't know if that's possible
Thank you
-
Yes, this is definitely possible!
All you need to do is to say that if the date in the "1st Delivery Date" column is in the past, the column should show "Red". Otherwise (if the date is Today or in the Future), then show "Yellow".
Try this:
=IF([1st Delivery Date]@row < TODAY(), "Red", "Yellow")
Keep in mind that blank cells will be read as "in the past" which means that if the "1st Delivery Date" is blank it will be Red.
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you. This was helpful and the formula worked
-
No problem! I'm glad I could help. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!