Smartsheet formulas

Give this a shot...

=IF(ISDATE([Design Required By]@row), [Design Required By]@row - IF([RISK(H/M/L) CAT (0,1,2,3)]@row = 2, 84, 28)

The @row replaces the row number and tells the formula to just look at the column specified on whatever row the formula is on. It makes formulas more efficient and avoids the worry of accidentally typing in the wrong row number.

Here's a breakdown of the formula, but judging by your screenshot, it will need adjusted to accommodate more than 2 different selections.

.

=IF(ISDATE([Design Required By]@row), [Design Required By]@row - IF([RISK(H/M/L) CAT (0,1,2,3)]@row = 2, 84, 28))

.

=IF(ISDATE([Design Required By]@row),

This part means says to run the formula if the cell in the row the formula is on in the [Design Required By] column is a date.

.

[Design Required By]@row -

If it is a date, then we want to take that date and subtract a number of days.

.

IF([RISK(H/M/L) CAT (0,1,2,3)]@row = 2,

If the [RISK(H/M/L) CAT (0,1,2,3)] column on the formula row equals 2

.

84,

Then output 84 (days which is 12 weeks to subtract from the original date).

.

28))

Otherwise (if the above mentioned cell does not equal 2) output 28 (days which is 4 weeks to subtract from the original date).

.

.

I do see that you also have a zero in your screenshot though which leads me to believe there is the possibility of the CAT also being a 3.

The formula I have provided should give you a date 12 weeks prior for the CAT being a 2 and 4 weeks for the CAT being a 1.

.

If you need to account for 0 and 3, let me know. Otherwise the formula provided should be working. Can you copy/paste the formula that is giving you an error?

Happy to help! 

Can you provide links to the posts in question? I can't promise that I'll be able to help, but I will definitely take a look...

I have replied to both.