Alert Someone on every day a Task is marked as happening

Milton Rodas

Hello,

Can I create an Alert (Alert Someone) that would send a notification to an email for each day when a task is listed as happening in relation to the Start and Finish dates, inputted in the system?

The logic cannot be static, and should be based on when a task Starts and when it is listed as Finished. Dates can vary. Am I explaining myself right?

See image example ... I hope this can be understood and hope there is a way to do this. Thank you!

Ella

I am not aware of a custom notification based on duration.

The workaround is to create a completion/status column. Do you have something like this in your sheet? You can set an alert/reminder to go out until a complete status or date is entered in that column.

In your case, what happens if they complete the task on 1/29 for example? Are you still going to want to send an e-mail until the original Finish Date 1/31 even though the task is complete?

Andrée Starå

Hi Milton,

To add to Ella's excellent advice.

I’d recommend creating a so-called helper column with a formula that checks a checkbox if today's date (current date) is between the Start and End date, and that would then trigger to send the Alert. (maybe also add Ella's part so it doesn't send if the task is complete)

Would that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer! 

Paul Newcome

Try a formula such as

=IF(AND(TODAY() >= Start@row, TODAY() <= Finish@row), 1)

This will check your box.

Then you can set up your automation to send out daily at a specific time with the condition of the box being checked. This will send only rows who's boxes are checked based on today's date being greater than or equal to the Start as well as less than or equal to the Finish.

If there are no boxes checked, no alert will be sent since the condition has not been met.

Ella

I am not aware of a custom notification based on duration.

The workaround is to create a completion/status column. Do you have something like this in your sheet? You can set an alert/reminder to go out until a complete status or date is entered in that column.

In your case, what happens if they complete the task on 1/29 for example? Are you still going to want to send an e-mail until the original Finish Date 1/31 even though the task is complete?

Andrée Starå

Hi Milton,

To add to Ella's excellent advice.

I’d recommend creating a so-called helper column with a formula that checks a checkbox if today's date (current date) is between the Start and End date, and that would then trigger to send the Alert. (maybe also add Ella's part so it doesn't send if the task is complete)

Would that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer! 

Milton Rodas

I did a few searches on how to make such a formula, and I guess I'm missing something.

I tried creating the Formula right at the checkbox column, is this correct?

This is what I have been trying to make work: =IF DATE => [Start] AND =< [Finish]

It gives me error #UNPARS

Milton Rodas

Hello Ella, regarding one of your questions, yes...even if the task is complete on 1/29, the system should send an email until 1/31 if the task had a Finish date of 1/31.

Milton Rodas

I also tried something like this, but did not work: =IF [Start] => TODAY AND [Finish] =< TODAY

• Basically, I think the idea of checking a box if any range of the dates is equal to Today, is good.
• Then, the Alert Someone function could be used if that checkbox is checked, then send an email.
• Is this possible?

I'll appreciate any help.

Paul Newcome

Try a formula such as

=IF(AND(TODAY() >= Start@row, TODAY() <= Finish@row), 1)

This will check your box.

Then you can set up your automation to send out daily at a specific time with the condition of the box being checked. This will send only rows who's boxes are checked based on today's date being greater than or equal to the Start as well as less than or equal to the Finish.

If there are no boxes checked, no alert will be sent since the condition has not been met.

Milton Rodas

This has been great. Thank you, Ella, Andrée, and Paul! The checkbox checks within the current date ranges and I've set the automation to alert someone when the checkbox is checked, and I've asked it to run the workflow "Daily".

Do you know what time of the day the alert is sent when Run workflow is set to "Daily"?

Paul Newcome

@Milton Rodas You have the trigger for when the box changes to checked. This will only run the one time the box becomes checked instead of every day for the duration like you wanted.

You will want to change the trigger to a date, then select the option to customize. Then you can set it to run every day. After that, you can set the time. The checkbox would;d go in the Condition portion of your alert.

Ella

Hi Paul, I like the formula idea very much! Perhaps I am missing something, but how are you going to set the custom Alert up as the Start Dates will not all be equal to 1/27/20?

@Milton Rodas thank you for the clarifications.

Paul Newcome

@Ella

EDIT: After re-reading your question... The date shown in the screenshot is simply the date to start the recurrence on.

Take a look at my above screenshot. You can customize the date trigger to "Repeat every 1 Day" which basically tells it to run every day. The date specified in the screenshot is only when it starts. If you set it to the same date you are setting it up on, it will start running tomorrow (unless you specify a later time in the day). The date to start can even be set in the past, but obviously it won't start running until the next date/time is reached after setting it up. If you are building this out in a template, then the date will stay whatever date you set it at which means every time a new sheet is created from it, the start date will be in the past, and the alert will start running immediately.

If you set the CONDITION (not the trigger) to be the checkbox, it will only send rows where the box is checked. If no rows are checked, then the condition is not met, and it won't send anything.

Milton Rodas

Hi Ella, I found Paul's last comment to be very useful...but I will see if it works tomorrow morning at 5 AM, the time I set it to. :) I think it will. Basically, the workflow would be set to alert someone every day IF (using the Conditions option, as mentioned by Paul) the Checkbox is checked on that day.

Following the formula, we know the checkbox will be checked whenever Today is equal or greater than Start date and equal or lesser than Finish.

Paul Newcome

@Milton Rodas That's the idea. Please let us know how it goes!

Ella

@Paul Newcome thanks, I get your idea. I am just not sure how the trigger will execute.

In the Alert set up we have the trigger "When a date is reached" run daily starting on 1/27/20. So these 2 conditions need to be met along with the check box condition for the alert to go out. But what if the date is 1/30/20 on the next row, will the trigger be valid there since the alert set up is based on 1/27/20? Technically we are not reaching the 1/27/20 date so I am thinking it won't go out, but may be I am wrong.

I am super curious so I set up a mock on my end to test.

Paul Newcome

The 1/27/20 date is only telling the Automation when to START. After that it will run daily looking for any row where the box is checked.

That 1/27/20 has nothing to do with the sheet itself. It is strictly for when the automation is to start looking daily. Any day after 1/27/20 will trigger the automation which will check for the condition of the checkbox before sending a row.

Andrée Starå

@Milton Rodas

Happy to help! 

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée