How to trigger dropdown to populate projected completed date based on specific complexity?

NBKD

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

Kelly P.

@NBKD

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?

NBKD

@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

Kelly P.

@NBKD

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!

NBKD

@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:

Kelly P.

@NBKD

Make sure that both the "Record Date: Complexity" and "Projected Complexity Completion" columns are Date columns.

NBKD

@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?

Kelly P.

@NBKD

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.

NBKD

@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.

Kelly P.

@NBKD

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!

NBKD

@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?

NBKD

@Kelly P. Disregard! I was able to figure it out thank you!!

Kelly P.

@NBKD

Okay, great!