Help

Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

Help with Complex Compound Conditional Formula

Topic Labels: Formulas
Solved
Jump to Solution
2233 10
cancel
Showing results for 
Search instead for 
Did you mean: 
Sarah_Saenz
5 - Automation Enthusiast
5 - Automation Enthusiast

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

10 Replies 10
Sarah_Saenz
5 - Automation Enthusiast
5 - Automation Enthusiast

That worked!! Thank you so much!!