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

[Sarah_Saenz]

That worked!! Thank you so much!!