Formula variable with checkbox and mutli-column criteria

Dana S

I am trying to return a date based on several criteria.

IF PSA/Lease Executed box is check, then use PSA/Lease Target Date + DD Period days

IF PSA/Lease Executed box is unchecked, then use PSA/Lease Executed Date + DD Period days

Here is what I tried but it is not correct. Can someone help me with this formula build?

=IF([PSA/Lease Executed]@row, 0, [PSA/Lease Target Date]@row+[DD Period]@row, (IF([PSA/Lease Executed]@row, 1, [PSA/Lease Execution Date]@row+[DD Period]@row, "")

Paul Newcome

Try this:

=IF([PSA/Lease Executed]@row = 1, [PSA/Lease Target Date]@row, [PSA/Lease Executed Date]@row) + [DD Period]@row

Dana S

This returned a sytax error. Does it need to be nested IF AND formula?

IF UNCHECKED return PSA/Lease Target Date + DD Period

IF CHECKED return PSA/Lease Executed Datee + Period

Paul Newcome

What error exactly? If you have it set as a column formula, try converting it into a cell formula then entering the new formula so we can see exactly what error it is which should provide some help in troubleshooting.

The formula above says

IF [PSA/Lease Executed]@row is checked, then output [PSA/Lease Target Date]@row, otherwise output [PSA/Lease Executed Date]@row. Then we add [DD Period]@row to whichever date is output by the IF statement.

I do see now where I read it backwards though, but that wouldn't return a syntax error.

Here it is saying that if it is unchecked then output target, otherwise output executed. Then add period.

=IF([PSA/Lease Executed]@row <> 1, [PSA/Lease Target Date]@row, [PSA/Lease Executed Date]@row) + [DD Period]@row

Dana S

It worked. Thank you so much Paul! Have a great weekend.

Paul Newcome

Happy to help. 👍️