Best Of

Hello,

Is there a way to sent an automation reminder to be triggered on my project plan for a task when another task's due date is 3 weeks out. For example, I have task A, task B and task C. Task B and Task C need to be completed before Task A can be completed. I am wanting to create an automation to notify the assignee that if Task B and Task C's status are not marked as completed, when Task A's due date is within 3 weeks, to send a reminder to complete task B and C.

Thank you!

Hi @ChristinaV

Yes, it’s possible to set up an automation to trigger a reminder when a related task’s due date is within 3 weeks.
I built a solution using a few helper columns and formulas. Let me explain step-by-step, referring to the screenshot I attached.

https://app.smartsheet.com/b/publish?EQBCT=862b45982e104be38743dbea26baf670

Formulas

[ due date is within 3 weeks] =(Finish@row - TODAY()) <= 21
[Successors] =COUNT(SUCCESSORS([Task Name]@row))
[S1] =IFERROR(INDEX([ due date is within 3 weeks]:[ due date is within 3 weeks], (IF(Successors@row >= 1, INDEX(SUCCESSORS([Task Name]@row), 1, 1)))), false)
[S2] =IFERROR(INDEX([ due date is within 3 weeks]:[ due date is within 3 weeks], (IF(Successors@row >= 2, INDEX(SUCCESSORS([Task Name]@row), 1, 2)))), false)
[S3] =IFERROR(INDEX([ due date is within 3 weeks]:[ due date is within 3 weeks], (IF(Successors@row >= 3, INDEX(SUCCESSORS([Task Name]@row), 1, 3)))), false)
[Send Alert] =AND(OR([S1]@row, [S2]@row, [S3]@row), NOT(Status@row = "Completed"))

1. [due date is within 3 weeks]
This formula checks if a task’s Finish date is within the next 21 days from today.
Formula:

=[Finish]@row - TODAY() <= 21

If the task’s due date is within 3 weeks, it returns true. Otherwise, it returns false.

2. [Successors]
This formula counts how many successor tasks each task has.
Formula:

COUNT(SUCCESSORS([Task Name]@row))

This is important because we want to know if Task A has successors like Task B and Task C depending on it.

3. [S1], [S2], [S3]
These formulas check whether the 1st, 2nd, and 3rd successor tasks are within 3 weeks.

=IFERROR(INDEX([due date is within 3 weeks]:[due date is within 3 weeks], (IF(Successors@row >= 1, INDEX(SUCCESSORS([Task Name]@row), 1, 1)))), false)
=IFERROR(INDEX([due date is within 3 weeks]:[due date is within 3 weeks], (IF(Successors@row >= 2, INDEX(SUCCESSORS([Task Name]@row), 1, 2)))), false)
=IFERROR(INDEX([due date is within 3 weeks]:[due date is within 3 weeks], (IF(Successors@row >= 3, INDEX(SUCCESSORS([Task Name]@row), 1, 3)))), false)

Detailed explanation of [S1], [S2], and [S3]:
Each formula works like this:

• It looks at the current task and identifies the first (or second, third) successor task.
• Then it checks if that successor’s [due date is within 3 weeks] value is true.
• If yes, it returns true; if no, it returns false.
• If no successor exists for that slot, it safely returns false using IFERROR.

4. [Send Alert]
This formula determines if a reminder should be triggered:

=AND(OR([S1]@row, [S2]@row, [S3]@row), NOT(Status@row = "Completed"))

• It checks if any of the successors have their due date within 3 weeks (OR([S1], [S2], [S3])).
• It also checks if the current task's Status is not Completed.
• If both conditions are met, Send Alert becomes true.

How does this answer your question:

In your example, Task B and Task C are prerequisites for Task A.

• When Task A’s due date is within 3 weeks, we check if Task B and/or Task C are still incomplete.
• If yes, the [Send Alert] box will be checked for Task B or Task C (depending on who needs to act).

Then, you can easily create a Smartsheet automation:

• Trigger: When [Send Alert] changes to checked
• Action: Send an alert to the assigned person to complete their task.