Creating Formulas for Status, and Task Health to send Email Notifications

Dee Spann

Hello, Everyone

I need help with my syntax on some formulas and sending email notifications.

• Status

○ If the %Complete is 0%, Status = Not Started

○ If the %Complete is between 1%-99%, Status = In Progress

○ If the %Complete is 100% = Complete

§ Questions: What if the task is no longer needed? In MS Project, we would inactivate the task.

• Task Health

○ If the Status is Complete = Blue

○ If the Status is In Progress and Finish Date is greater than 5 days from today = Green

○ If the Status is In Progress and the Finish Date is less than 5 days from today = Yellow

○ If the Status is In Progress and the Finish Date has passed = Red

○ If the Status is Not Started and the Start Date has passed = Red

• Email Notification

○ If Task Health = Yellow, send a notification every 2 days to PM and Resource

○ If Task Health = Red, send a notification every day to PM, Resource, BRM

• Color Formatting for Rows (Optional)

○ If the Status is Complete row is Gray

○ If the Task Health is Yellow is Yellow 

○ If the Task Health is Red row is Light Red

Paul Newcome

Status: There are a number of solutions in this thread that should help you get started. These are called "Nested IFs" or "Nested IF Statements".

https://community.smartsheet.com/discussion/29591/multiple-if-statements

Task Health: Very similar to Status. Nested IFs but this time with the AND function. Most posts here in the Community are going to have something along the lines of "RYG" if this doesn't help and you want to search further.

https://community.smartsheet.com/discussion/68283/ryg-ball-with-nested-if-statement-combined-with-and

Email Notification: You would set the yellow automation up to be a date based trigger and select the option for custom recurrence. That's where you can set it to run every other day. Then you would set a condition of the RYG column being Yellow. Red would be the same thing except the custom recurrence would be every 1 day and the condition would be when the RYG column is Red. In the below help article, you would click where ti says "Run Once" to access the custom options.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/alerts-reminders

And the last one would be accomplished using Conditional Formatting.

https://help.smartsheet.com/learning-track/level-1-get-started/conditional-formatting