Hello,
I’m looking for assistance with a Smartsheet formula I’m working on and could use a second set of eyes.
What I’m trying to achieve:
I’m currently working on an outreach program that needs to calculate the Next Outreach Date based on outreach activity. Our team makes up to three outreach attempts, and we also have an additional option to send a follow‑up fax, with up to three Fax Sent dates (Fax 1, Fax 2, Fax 3).
The goal is for the Next Outreach Date to be calculated based on whichever activity is appropriate next (outreach attempts and/or fax events).
Important constraint: No additional outreach attempts or fax activity should be scheduled once Date: Attempt 3 has been completed.
Current formula (works for outreach attempts/outcomes):
This formula currently works as expected for the outreach attempt dates and outcomes, but I need to incorporate the fax options as well:
=IF(AND(ISBLANK([DATE: Attempt 3]@row), OR([OUTCOME: Attempt 2]@row = "Left VM", [OUTCOME: Attempt 2]@row = "No Answer"), NOT(ISBLANK([DATE: Attempt 2]@row))), WORKDAY([DATE: Attempt 2]@row, 2), IF(AND(ISBLANK([DATE: Attempt 2]@row), OR([OUTCOME: Attempt 1]@row = "Left VM", [OUTCOME: Attempt 1]@row = "No Answer"), NOT(ISBLANK([DATE: Attempt 1]@row))), WORKDAY([DATE: Attempt 1]@row, 2), ""))
What I need help adding:
I need to add logic that also considers Fax Sent Date 1 / Fax Sent Date 2 / Fax Sent Date 3 when calculating the Next Outreach Date. If a fax is sent, the Next Outreach Date should update based on the most recent relevant fax activity (or where the workflow is in the sequence), similar to how the outreach attempt logic works today. I have added a screenshot at the bottom for field names.
Issue I’m running into:
I’m having trouble determining the best way to structure the formula so it consistently calculates the Next Outreach Date while factoring in both:
- outreach attempt dates/outcomes (up to 3 attempts), and
- fax sent dates (up to 3 fax sends).
Additional context:
For reference, the Next Outreach Date is currently set to be 2 business days after the most recent applicable activity (using WORKDAY(date, 2)), and I need the fax dates to be included in that same decision logic.
Any guidance or best‑practice recommendations would be greatly appreciated. Thank you in advance for your help.
This appears to be working just not sure it is logically correct:
=IF(OR(NOT(ISBLANK([DATE: Attempt 3]@row)), NOT(ISBLANK([OUTCOME: Attempt 3]@row))), "", IF(NOT(ISBLANK([DATE: Fax Follow Up Resent (2)]@row)), WORKDAY([DATE: Fax Follow Up Resent (2)]@row, 2), IF(NOT(ISBLANK([DATE: Fax Follow Up Resent (1)]@row)), WORKDAY([DATE: Fax Follow Up Resent (1)]@row, 2), IF(NOT(ISBLANK([DATE: Fax Follow Up Sent (Yes)]@row)), WORKDAY([DATE: Fax Follow Up Sent (Yes)]@row, 2), IF(AND(NOT(ISBLANK([DATE: Attempt 2]@row)), OR([OUTCOME: Attempt 2]@row = "Left VM", [OUTCOME: Attempt 2]@row = "No Answer")), WORKDAY([DATE: Attempt 2]@row, 2), IF(AND(ISBLANK([DATE: Attempt 2]@row), NOT(ISBLANK([DATE: Attempt 1]@row)), OR([OUTCOME: Attempt 1]@row = "Left VM", [OUTCOME: Attempt 1]@row = "No Answer")), WORKDAY([DATE: Attempt 1]@row, 2), ""))))))
