If/Then Statement to Return a specific date

Kia Gibbs · 06/21/21

Hello,

I am new to Smartsheet and though I feel like this should be a simple formula, I am stumped. I am hoping to create an automation that would calculate a due date from a date received based on the value selected from a drop down list.

So if I choose option a from a drop down list (Permit Type), then add 30 days to the date received to return a due date.

Any help with this would be greatly appreciated.

Paul Newcome · 06/21/21

How many different options do you have?

The absolute most straight forward response to your question would be...

=IF([Permit Type]@row = "Residential", [Date Rec.]@row + 30)

This would leave a blank for all other options. To include other options we could build out a nested IF statement which would depend on your exact criteria, and if you have a lot of different outputs from a lot of different options, it may be more efficient to build out a table and use a completely different formula to reference it for how many days to add.

Paul Newcome · 06/21/21

Since it is only 3 options, the nested IF is certainly the way to go. To create a nested IF, we first need to understand the 3 different portions of an IF statement.

=IF(this is true, output this, otherwise that)

=IF([Permit Type]@row = "Residential", [Date Rec.]@row + 30)

Something to keep in mind when nesting IFs... It will work from left to right and stop on the first true value. This means that if it makes it to the send IF, then the first must be false. If it makes it to the third IF, then the first two must be false. So on and so forth.

So let's use this logic and spell it out in "plain English" first:

If the [Permit Type] is "Residential" then we want to output 14. If the [Permit Type] is not "Residential", then we want to check to see if the [Permit Type] is "Commercial". If that is true then we want to output 30. If it is not "Residential" or "Commercial", then we are going to output 5 because that is the only option left.

Now lets take a look at working this into a formula.

=IF([Permit Type]@row = "Residential", 14, value if false)

We have our first IF statement in place. Based on the logic and out plain English, we want a similar IF statement for "Commercial" and we want it to run if the [Permit Type] being "Residential" is false.

So we have our "Commercial" IF:

IF([Permit Type]@row = "Commercial", 30, value if false)

And we drop that into the "value if false" portion of the "Residential" IF:

=IF([Permit Type]@row = "Residential", 14, value if false)

=IF([Permit Type]@row = "Residential", 14, IF([Permit Type]@row = "Commercial", 30, value if false))

Now the "value if false" portion is saying if BOTH are false. This means that since we only have 3 options, if both are false then the third MUST be true. In that case we don't have to specify anything about "Trade Applications". We already know that if both of the previous are false then "Trade Applications" must be true, so we can go ahead and drop that output into the remaining "value if false" portion:

=IF([Permit Type]@row = "Residential", 14, IF([Permit Type]@row = "Commercial", 30, value if false))

=IF([Permit Type]@row = "Residential", 14, IF([Permit Type]@row = "Commercial", 30, 5))

And there you have it. A nested IF to output 1 of three different numbers based on what is in the [Permit Type] column.

Now we only have to add that to the existing date, and we are all wrapped up.

=[Date Rec.]@row + output from nested IF

=[Date Rec.]@row + IF([Permit Type]@row = "Residential", 14, IF([Permit Type]@row = "Commercial", 30, 5))

Paul Newcome · 06/21/21

How many different options do you have?

The absolute most straight forward response to your question would be...

=IF([Permit Type]@row = "Residential", [Date Rec.]@row + 30)

This would leave a blank for all other options. To include other options we could build out a nested IF statement which would depend on your exact criteria, and if you have a lot of different outputs from a lot of different options, it may be more efficient to build out a table and use a completely different formula to reference it for how many days to add.

Kia Gibbs · 06/21/21

Wow! Thank you @Paul Newcome!!!!!! Seriously thank you! It returned a date! I don't know where the mistake was in my formula.

I have 3 options in the drop down, Residential, Commercial and Trade Applications.

So how do I approach the "nested formula", to include the values of the other 2 options?

Residential = +14 days (corrected from example)

Commercial = +30 days

Trade Applications = +5 days

Paul Newcome · 06/21/21