Help with Complex Compound Conditional Formula

[Sarah_Saenz]

I have a base of events where I am tracking event updates. I am trying to write a conditional formula that will display up to 3 different possible date fields based on 11 outcomes using data from fields already existing in each record.

I have tried nested IF formulas using IF( AND( as well as IF( OR( and have not been able to correctly solve the problem.
I am either getting a formula that produces a date that is not 100% reflective of all criteria or an error message.
I looked into SWITCH formulas but got lost…

Fields within this base include:
Field Type | Field Content
Date Field | Original Event Date
Single Select Field | Update to the event (cancel, postpone, reschedule)
Date Field | Announcement of Update
Date Field | Rescheduled event date
Single Select Field | 2nd Update to the event (cancel, postpone, reschedule)
Date Field | Announcement of 2nd Update
Date Field | 2nd Rescheduled Date

I want to display the appropriate date for each record based on the following criteria:

Return {Original Event Date} if any of the following are true:

1. If the single select field {Update} is “cancel”
2. If the single select field {Update} is “postpone”
3. If the single select field {Update} is “reschedule” AND the date field {Announcement of Update} is after today
4. If the single select field {Update} is “postpone” AND the single select field {2nd Update} is “cancel”
5. If the single select field {Update} is “postpone” AND the single select field {2nd Update} is “reschedule” AND the date field {Announcement of 2nd Update} is after today

Return {Rescheduled Event Date} if any of the following are true:

1. If the single select field {Update} is “reschedule” AND the date field {Announcement of Update) is on/before today
2. If the single select field {Update} is “reschedule” AND the single select field {2nd Update} is “cancel”
3. If the single select field {Update} is “reschedule” AND the single select field {2nd Update} is “postpone”
4. If the single select field {Update} is "reschedule AND the the single select field {2nd Update} is “reschedule” AND the date field {Announcement of 2nd Update} is after today
5. If the single select filed {Update} is “postpone” AND the single select field {2nd Update} is “reschedule” AND the date field {Announcement of 2nd Update} is after today

Return {2nd Rescheduled Event Date} if any of the following are true:
If the single select field {Update} is "reschedule AND the the single select field {2nd Update} is “reschedule” AND the date field {Announcement of 2nd Update} is on/before today.

Any help would be greatly appreciated!

Thanks,
Sarah

[kuovonne]

This certainly is a complex situation.

At first glance, you will need a combination of IF, AND and OR statements nested together.

You might be able to use the SWITCH function for parts of the formula, but only when evaluating the single select fields. You cannot use SWITCH when comparing dates.

Do you have a matrix or grid showing all the different possible situations? It would be easier to build a formula based on a grid showing all the different possible combinations.

It seems like some of your combinations overlap. For example in the list below, situations 4 and 5 are subsets of situation 2. Either situation 2 isn’t fully defined, or situations 4 & 5 aren’t necessary.

Sarah_Saenz:

Return {Original Event Date} if any of the following are true:

1. If the single select field {Update} is “cancel”
2. If the single select field {Update} is “postpone”
3. If the single select field {Update} is “reschedule” AND the date field {Announcement of Update} is after today
4. If the single select field {Update} is “postpone” AND the single select field {2nd Update} is “cancel”
5. If the single select field {Update} is “postpone” AND the single select field {2nd Update} is “reschedule” AND the date field {Announcement of 2nd Update} is after today

[Sarah_Saenz]

Thank you for your feedback! I do think that the question you posed may be answered by adding an AND OR to situation 2.

Here is what I’ve put together as a matrix to show all the different combinations and outcomes I’d like to display. There is a chance some events may have 3rd reschedules, so I want to be aware of scaling that when building out the formula.

Thank you!
s

[kuovonne]

Thank you for the screen capture. However, it isn’t clear what the values are for the different input fields. Could you do a different screen capture showing all the relevant fields {Update}, {2nd Update}, {Announcement of Update}, {Announcement of 2nd Update}, etc?

[Sarah_Saenz]

Hi Kuovonne,

Here is a screen capture of the different fields within the base - show formula is the field I am trying to correct.
In addition to the lookup fields {Link to Show Date}, {Link to Reschedule}, {Link to 2nd Reschedule} I do have the original date fields {Show Date}, {Reschedule Date}, {2nd Reschedule Date} in the table as well…if that makes any difference in constructing the formula.

In this example:

• the record with date formula reflecting 10/15/2020 (first record with “reschedule” in the 2nd announce field) should be reflecting the original show date (5/14/20) because the 2nd announcement (reschedule) has not yet been made
• the record with date formula reflecting 4/11/2021 (bottom record) IS currently reflecting the correct date because the 2nd Reschedule date HAS been announced

[kuovonne]

Thanks for the screen capture.

In general, if you have access to the original date fields, it is far better to use the original date field than the lookup fields. Lookup fields and date fields cause more headaches in formulas than any other field types. A lookup of a date field is the most difficult to troubleshoot.

Would you be able to create a sharable link to a simplified version of the table with the data so that I could make a copy to work with?

• Include all the input fields, but no additional fields. (e.g. if the date exists in both a date field and a lookup field, include only the original date field)
• Include a new single line text field {Desired formula result}, with the name of the field that holds the desired date. (E.g. this field would say “Show Date” or “Rescheduled Event Date” or “2nd Rescheduled Event Date”, not the actual date.)

[Sarah_Saenz]

Here you go

Airtable

UPDATES 2nd → 1st - Airtable

Explore the "UPDATES 2nd → 1st" view on Airtable.

[kuovonne]

Thanks for the the shared link.

Here is a first stab at the formula. I wasn’t able to test it because the shareable link you created did not let me make a copy of your base. There may be some errors in field names, extra/missing commas, that I can’t test unless you make your table copyable.

I also had some trouble identifying the logic based on announcement dates. But hopefully this formula will give you enough of a starting point.

SWITCH({2nd UPDATE},
  "CANCEL", SWITCH(UPDATE,
              "CANCEL", {Show Date (original)},
              "POSTPONE", {Show Date (original)},
              "RESCHEDULE", {Resched Date}
            ),
  "POSTPONE", SWITCH(UPDATE,
                "RESCHEDULE", {Resched Date}
              ),
  "RESCHEDULE", SWITCH(UPDATE,
                  "POSTPONE", IF({2nd Announce},
                                {Resched Date},
                                {Show Date (original)}
                              ),
                  "RESCHEDULE", IF({2nd Announce},
                                  {2nd Resched Date},
                                  {Resched Date}
                                )
                ),
  SWITCH(UPDATE,
             "CANCEL", {Show Date (original)},
             "POSTPONE", {Show Date (original)},
             "RESCHEDULE", {Resched Date}
  ),
  {Show Date (original)}
)

[Sarah_Saenz]

kuovonne:

SWITCH({2nd UPDATE},
  "CANCEL", SWITCH(UPDATE,
              "CANCEL", {Show Date (original)},
              "POSTPONE", {Show Date (original)},
              "RESCHEDULE", {Resched Date}
            ),
  "POSTPONE", SWITCH(UPDATE,
                "RESCHEDULE", {Resched Date}
              ),
  "RESCHEDULE", SWITCH(UPDATE,
                  "POSTPONE", IF({2nd Announce},
                                {Resched Date},
                                {Show Date (original)}
                              ),
                  "RESCHEDULE", IF({2nd Announce},
                                  {2nd Resched Date},
                                  {Resched Date}
                                )
                ),
  SWITCH(UPDATE,
             "CANCEL", {Show Date (original)},
             "POSTPONE", {Show Date (original)},
             "RESCHEDULE", {Resched Date}
  ),
  {Show Date (original)}
)

Thank you for this! The formula is giving me an error message - invalid formula. I don’t know much about switch formulas, but I’m wondering if every use of UPDATE should be written as {UPDATE} - that is trying to reference that field, correct? I will give that a try and will go thru looking for missing/extra commas.
I do have the toggle “on” to allow viewers to copy data out of this view. Is that what you meant by making the table copyable?

Thanks again for your time on this!

[kuovonne · Accepted solution]

Try this one. I moved some of the lines around at the end.

SWITCH({2nd UPDATE},
  "CANCEL", SWITCH(UPDATE,
              "CANCEL", {Show Date (original)},
              "POSTPONE", {Show Date (original)},
              "RESCHEDULE", {Resched Date}
            ),
  "POSTPONE", SWITCH(UPDATE,
                "RESCHEDULE", {Resched Date}
              ),
  "RESCHEDULE", SWITCH(UPDATE,
                  "POSTPONE", IF({2nd Announce},
                                {Resched Date},
                                {Show Date (original)}
                              ),
                  "RESCHEDULE", IF({2nd Announce},
                                  {2nd Resched Date},
                                  {Resched Date}
                                )
                ),
  SWITCH(UPDATE,
             "CANCEL", {Show Date (original)},
             "POSTPONE", {Show Date (original)},
             "RESCHEDULE", {Resched Date},
             {Show Date (original)}
  )
)