Automation Workflow Asssistance - Trigger based off Task's due date

ChristinaV

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!

ChristinaV

Hello,

Thank you! I am trying to check the whole sheet 'Due in 3 weeks' column and not just the specific row. I only have the formula to check 'due in 3 weeks' on the one specific task I need to base everything else off of. But I think I figured it out! There was a hidden row that I was not seeing that still had the formula applied to check off 'due in 3 weeks'. Once that was removed it worked correctly :)

Thank you,

Christina

jmyzk_cloudsmart_jp

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.

ChristinaV

Hello,

This is super helpful!

I took your response and changed it a little based on our project plan. I create a column called 'Due in 3 weeks' that only applies to the one specific task I want the other tasks to trigger based off of. I then created another check box that checks if the other tasks are not complete and 'Due in 3 weeks' has a count of one (indicating that the one task is due in three weeks) to check the box.

=IF(AND(Status@row <> "Complete", COUNTIF([Due in 3 weeks]:[Due in 3 weeks], 1) > 0), 1, 0)

However, I am having an issue with the boxes not unchecking when it becomes untrue. If I change the due date so 'Due in 3 weeks' is no longer checked, it does not retroactively go back and update the other check boxes. Is there a way to do that?

jmyzk_cloudsmart_jp

Hi @ChristinaV

You are very close!
The issue is not with your overall logic but with the use of COUNTIF.
By using COUNTIF([Due in 3 weeks]:[Due in 3 weeks], 1) > 0, you are checking the entire sheet, not just the specific row.
This causes the checkbox to stay checked even when the due date changes because it still finds other rows with "Due in 3 weeks" marked as 1.

Instead, you should check only the current row's "Due in 3 weeks" value.

Here are two examples that work correctly:

=IF(AND(Status@row <> "Complete", [Due in 3 weeks]@row), 1, 0)

or, if you want to handle cases where the Status field might contain variations like "Completed", you can use a CONTAINS

=IF(AND(NOT(CONTAINS("Complete", Status@row)), [Due in 3 weeks]@row), true)

I originally used "Completed" as the status value in my example, but I adjusted it to match your use of "Complete".
Either formula will ensure the checkbox turns off automatically if the due date condition is no longer true.

Note:
When referencing a checkbox column in Smartsheet, remember that checkbox columns are Boolean (true/false). This means you can directly use the checkbox column value inside logic functions like AND(), IF(), etc.

For example:
You can write:

=IF([Checkbox Column]@row, "Yes", "No")

There is no need to specifically compare the checkbox column to = true unless you want to make it extra explicit.

Similarly, when combining conditions:

=AND(Status@row <> "Complete", [Due in 3 weeks]@row)

works perfectly because [Due in 3 weeks]@row already returns true or false.

https://app.smartsheet.com/b/publish?EQBCT=4f45833797114024bcea3d94dd171360

Scarlet Johns

Yes, you can set this up using conditional automation in tools like Asana, Monday.com, or ClickUp. Create a rule that triggers when Task A’s due date is 3 weeks away, then checks if Task B and C are incomplete, and sends reminders to their assignees. Use dependencies and custom logic to tie the tasks together—most platforms support this with a bit of creative setup using integrations or advanced automation.

ChristinaV

Hello,

Thank you! I am trying to check the whole sheet 'Due in 3 weeks' column and not just the specific row. I only have the formula to check 'due in 3 weeks' on the one specific task I need to base everything else off of. But I think I figured it out! There was a hidden row that I was not seeing that still had the formula applied to check off 'due in 3 weeks'. Once that was removed it worked correctly :)

Thank you,

Christina

jmyzk_cloudsmart_jp

Glad you figured it out!😁