Creating Formulas for Status, and Task Health to send Email Notifications
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
Answers
-
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".
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.
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.
And the last one would be accomplished using Conditional Formatting.
-
Thank you, I will review the information you provided.
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
- 142 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!