I want to send automated email to "assigned to", from a sheet, but only one reminder email vs mutlip

Joyjenz

I have title, "assigned to" column, status(task 1), due date (task 1) columns, status(task 2), due date(task 2)…it goes for at least 10 tasks. The conditions to trigger the emails are,

1. trigger 2 days before due date(when status is not equal to completed) and leave weekends
2. trigger on due date(when status is not equal to completed) and leave weekend)
3. trigger when overdue(when status is not equal to completed) and leave weekends

Instead of sending multiple reminders for each task, I am planning to send only one reminder email, per row, if any of the above conditions met.

I've drafted a formula to be updated on helper column and use the column to determine when the automation to be triggered. But the formula is too long and not sure if that can work. Do we have anyother way to simplify this?

Formula

=IF(OR(AND(Status 1 <> "Completed", WEEKDAY(Due Date 1) <> 1, WEEKDAY(Due Date 1) <> 7, Due Date 1 >= TODAY(), Due Date 1 <= TODAY() + 2),

AND(Status 2 <> "Completed", WEEKDAY(Due Date 2) <> 1, WEEKDAY(Due Date 2) <> 7, Due Date 2 >= TODAY(), Due Date 2 <= TODAY() + 2),

AND(Status 3 <> "Completed", WEEKDAY(Due Date 3) <> 1, WEEKDAY(Due Date 3) <> 7, Due Date 3 >= TODAY(), Due Date 3 <= TODAY() + 2),

AND(Status 4 <> "Completed", WEEKDAY(Due Date 4) <> 1, WEEKDAY(Due Date 4) <> 7, Due Date 4 >= TODAY(), Due Date 4 <= TODAY() + 2),

AND(Status 5 <> "Completed", WEEKDAY(Due Date 5) <> 1, WEEKDAY(Due Date 5) <> 7, Due Date 5 >= TODAY(), Due Date 5 <= TODAY() + 2),

AND(Status 6 <> "Completed", WEEKDAY(Due Date 6) <> 1, WEEKDAY(Due Date 6) <> 7, Due Date 6 >= TODAY(), Due Date 6 <= TODAY() + 2),

AND(Status 7 <> "Completed", WEEKDAY(Due Date 7) <> 1, WEEKDAY(Due Date 7) <> 7, Due Date 7 >= TODAY(), Due Date 7 <= TODAY() + 2)

), "Upcoming",

IF(OR(

AND(Status 1 <> "Completed", WEEKDAY(Due Date 1) <> 1, WEEKDAY(Due Date 1) <> 7, Due Date 1 = TODAY()),

AND(Status 2 <> "Completed", WEEKDAY(Due Date 2) <> 1, WEEKDAY(Due Date 2) <> 7, Due Date 2 = TODAY()),

AND(Status 3 <> "Completed", WEEKDAY(Due Date 3) <> 1, WEEKDAY(Due Date 3) <> 7, Due Date 3 = TODAY()),

AND(Status 4 <> "Completed", WEEKDAY(Due Date 4) <> 1, WEEKDAY(Due Date 4) <> 7, Due Date 4 = TODAY()),

AND(Status 5 <> "Completed", WEEKDAY(Due Date 5) <> 1, WEEKDAY(Due Date 5) <> 7, Due Date 5 = TODAY()),

AND(Status 6 <> "Completed", WEEKDAY(Due Date 6) <> 1, WEEKDAY(Due Date 6) <> 7, Due Date 6 = TODAY()),

AND(Status 7 <> "Completed", WEEKDAY(Due Date 7) <> 1, WEEKDAY(Due Date 7) <> 7, Due Date 7 = TODAY())

), "Due Today",

IF(OR(

AND(Status 1 <> "Completed", WEEKDAY(Due Date 1) <> 1, WEEKDAY(Due Date 1) <> 7, Due Date 1 < TODAY()),

AND(Status 2 <> "Completed", WEEKDAY(Due Date 2) <> 1, WEEKDAY(Due Date 2) <> 7, Due Date 2 < TODAY()),

AND(Status 3 <> "Completed", WEEKDAY(Due Date 3) <> 1, WEEKDAY(Due Date 3) <> 7, Due Date 3 < TODAY()),

AND(Status 4 <> "Completed", WEEKDAY(Due Date 4) <> 1, WEEKDAY(Due Date 4) <> 7, Due Date 4 < TODAY()),

AND(Status 5 <> "Completed", WEEKDAY(Due Date 5) <> 1, WEEKDAY(Due Date 5) <> 7, Due Date 5 < TODAY()),

AND(Status 6 <> "Completed", WEEKDAY(Due Date 6) <> 1, WEEKDAY(Due Date 6) <> 7, Due Date 6 < TODAY()),

AND(Status 7 <> "Completed", WEEKDAY(Due Date 7) <> 1, WEEKDAY(Due Date 7) <> 7, Due Date 7 < TODAY())

), "Overdue", "")))

Adam Murphy

You could add a helper column per task and simplify it that way, but otherwise you will have a long formula for sure. I think the limit is 4000 characters in a cell, so it should still work.