Status Update Formula
Dear Friends
I am new to Smartsheet and like to learn quickly from your experience. I have tried to update the status column based on the % completed byt this simple logic
0%--. Not started,
Greater then 0 % but < 100% - WIP
100% - completed
How I can make this more meaningful by connecting with duration or end date ?
For example--
status = Not started- Delayed (ORANGE COLOR) - if % complete = "0" and today's date is greater than start date-
status = Overdue- Delayed (RED COLOR) - if % complete < "100" and today's date is greater than end date-
status = WIP - AT RISK (YELLOW COLOR) - if % complete is Greater then 0 % but < 50% and Time elapsed 50% of duration
I am not able to figure this out. Will you please help ?
Thanks
Mitesh Desai
Answers
-
This is how to use nested if statements.
The formula for the status column is:
=IF(AND([Percent Complete]@row = 0, [Percent Duration]@row > 0), "Not Started - Delayed", IF(AND([Percent Complete]@row > 0, [Percent Complete]@row < 1, [Percent Complete]@row > [Percent Duration]@row), "In Progress - On Track", IF(AND([Percent Complete]@row = 0, [Percent Duration]@row > 0), "Delayed - Overdue", IF(AND([Percent Complete]@row > 0, [Percent Complete]@row < 1, [Percent Duration]@row > 1), "In Progress - Overdue", IF(AND([Percent Duration]@row > 1, [Percent Complete]@row > 0), "In Progress - Overdue", IF(AND([Percent Duration]@row > 1, [Percent Complete]@row = 0), "Not Started - Overdue", ""))))))
The percent duration column formula is:
=(TODAY() - [Start Date]@row) / ([Estimated End Date]@row - [Start Date]@row)
Hopefully this helps and you can reverse engineer a few things.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives