Nested IF formula breaking out too early

Hi!

I have a series of columns describing deployment dates. Users can enter up to 8 deployment dates (1 per column) and an associated Type (of deployment) column:

Production Event Date 1 = 2/15/22

Production Event Date 1 - ON / OFF / LIVE = OFF

Production Event Date 2 = 2/20/22

Production Event Date 2 - ON / OFF / LIVE = ON/LIVE

Production Event Date 3 = 3/1/22

Production Event Date 3 - ON / OFF / LIVE = OFF

Production Event Date 4 = 3/15/22

Production Event Date 4 - ON / OFF / LIVE =ON/LIVE

...

I'm using the following code to run through each of the column groupings to fill another column with a list of deployment dates. I ultimately want a column that has a list of dates that are "OFF" and a list of dates that are "ON" - formatted for reading such as:

OFF deployments: "2/15/22, 3/1/22"

LIVE deployments: "2/20/22, 3/15/22"

The below is for finding all the "OFF" deployments:

=IF(OR(ISBLANK([Production Event - Date 1]@row), [Production Event (Date 1) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", [Production Event - Date 1]@row + IF(OR(ISBLANK([Production Event - Date 2]@row), [Production Event (Date 2) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 2]@row + IF(OR(ISBLANK([Production Event - Date 3]@row), [Production Event (Date 3) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 3]@row + IF(OR(ISBLANK([Production Event - Date 4]@row), [Production Event (Date 4) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 4]@row + IF(OR(ISBLANK([Production Event - Date 5]@row), [Production Event (Date 5) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 5]@row + IF(OR(ISBLANK([Production Event - Date 6]@row), [Production Event (Date 6) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 6]@row + IF(OR(ISBLANK([Production Event - Date 7]@row), [Production Event (Date 7) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 7]@row + IF(OR(ISBLANK([Production Event - Date 8]@row), [Production Event (Date 8) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 8]@row))))))))

The problem is that the first time an IF statement finds a TRUE logical expression, the whole statement ends with "success". I need to be able to account for Dates that are not sequential and multiple OFF or ON/LIVE dates.

Is there a way to do this in SmartSheet? I would do this in a FOR loop in a normal programming language.


Thanks for your help!

Tony

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!