Formula for Status
Hello,
I am trying to create a formula to automate the 'status' row in my project grid in SmartSheet. Here is what I am looking for:
Actual start date = Blank, show "Not Started"
If Actual Start Date = is a date and Actual end date = Blank, show "In Progress"
If Actual start date = blank and projected start date > today OR if Actual end date = blank and projected end date > today, show "Overdue"
Actual end date = is a date, show "Complete"
Can anyone help with the nested formula to cover all scenarios?
Best Answer
-
Try something like this...
=IF([Actual End Date]@row <> "", "Complete", IF([Actual Start Date]@row = "", IF([Projected Start Date]@row < TODAY(), "Overdue", "Not Started"), IF([Projected End Date]@row < TODAY(), "Overdue", "In Progress")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try something like this...
=IF([Actual End Date]@row <> "", "Complete", IF([Actual Start Date]@row = "", IF([Projected Start Date]@row < TODAY(), "Overdue", "Not Started"), IF([Projected End Date]@row < TODAY(), "Overdue", "In Progress")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you!! That worked. One further question...is there a way for the formula to show nothing in the event a date is not yet entered? From screenshot below - for tasks I don't yet have dates in, the formula auto-populates 'overdue'. Is there a way for it to be blank until a date is entered under projected start date?
-
Here you go...
=IF([Projected Start Date]@row <> "", IF([Actual End Date]@row <> "", "Complete", IF([Actual Start Date]@row = "", IF([Projected Start Date]@row < TODAY(), "Overdue", "Not Started"), IF([Projected End Date]@row < TODAY(), "Overdue", "In Progress"))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
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
Check out the Formula Handbook template!