How to trigger dropdown to populate projected completed date based on specific complexity?
I want to trigger these drop downs in the "Complexity" column to populate a date in the "Projected Complexity Completion" once a dropdown value is selected and once status changes to "active".
Low Complexity = 2 days
Medium Complexity = 5 days
Most Complex = 10 days
Answers
-
You could add a column ("Record Date: Complexity"), then set up an automation to fill that column with the date that "Complexity" is changed to one of the dropdown values, and finally put a formula in "Projected Complexity Completion" that adds the correct number of days to the date in the "Record Date: Complexity" column according to your criteria. Does that make sense?
-
@Kelly P. thank you! I added the new column and automation, but still stuck on what that formula looks like for all 3 different drop down values
-
Try this:
=IF(Complexity@row = "Low Complexity", [Record Date: Complexity]@row + 2, IF(Complexity@row = "Medium Complexity", [Record Date: Complexity]@row + 5, IF(Complexity@row = "Most Complex", [Record Date: Complexity]@row + 10)))
Let me know if this works for you!
-
@Kelly P. - I applied the formula and this is what I ended up getting, doing something a bit funky. It is adding the 2, 5, 10 to the end of the year of 23:
-
Make sure that both the "Record Date: Complexity" and "Projected Complexity Completion" columns are Date columns.
-
@Kelly P. thank you!! It works beautifully, I just need it to count working days only, is there a different formula for that vs the original or a setting option in Smartsheet?
-
There is a slightly different formula:
=IF(Complexity@row = "Low Complexity", WORKDAY([Record Date: Complexity]@row, 2), IF(Complexity@row = "Medium Complexity", WORKDAY([Record Date: Complexity]@row, 5), IF(Complexity@row = "Most Complex", WORKDAY([Record Date: Complexity]@row, 10))))
Note that you can also populate an earlier date using the WORKDAY function -- just put in a negative number of workdays instead of positive.
-
@Kelly P. for some reason some of the values are showing up as invalid (see below). Both columns are date fields for record date complexity and projected completion date.
-
That's because there's no date in the "Record Date: Complexity" column. Take out the "Medium Complexity" value from the "Complexity" column, save the sheet, and then re-enter the value and save the sheet again. Your automation should then populate the "Record Date: Complexity" column with today's date (11/10/23) and the formula will then populate the "Projected Complexity Completion" column with "11/17/23"
To eliminate the error message for the few seconds that it will take for the record date automation to populate the cell (and to eliminate the error for existing rows that have nothing in the record date column), use this formula in the "Projected Complexity Completion" column:
=IFERROR(IF(Complexity@row = "Low Complexity", WORKDAY([Record Date: Complexity]@row, 2), IF(Complexity@row = "Medium Complexity", WORKDAY([Record Date: Complexity]@row, 5), IF(Complexity@row = "Most Complex", WORKDAY([Record Date: Complexity]@row, 10)))), "")
Let me know if this works for you!
-
@Kelly P. I think it is happening because I have this condition. Whenever the status changes, it changes projected complexity to invalid. I need this to stay even if the status changes outside of "active" though, is there a way to prevent this from happening?
I do have an Active Date column, so maybe the automation can go away altogether and the formula in Projected Complexity column can be edited?
-
@Kelly P. Disregard! I was able to figure it out thank you!!
-
Okay, great!
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!