Alert Someone on every day a Task is marked as happening

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!

Find more posts tagged with

functionality

Workflows in Smartsheet

Email Notifications

Accepted answers

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.

All comments

Milton Rodas

@Ella @Paul Newcome ... so, today the notification went out at 5 AM, and it's funny:

In the email alert, it did include continuous dates, but it also included a date from yesterday 1/27/20 and it did not include any date of 1/28/20 (which is today).
Whereas, when I checked the smartsheet, the checkboxes were only checked for dates of 1/28/20 or dates that were continuous, and the task that had 1/27/20 was NOT checked (and yet, it was included in the report).

So, I think the formula works well inside the smartsheet itself, it does what it's being told to do, which is check the box if any date range includes today.

But the workflow is NOT working in sending out those lines that have a checkbox TODAY. Why did it send a task dated yesterday in today's report and did not send any task dated today?

SEE HERE:

This is what we received today. Notice the 1/27/20 date included in the report, whereas the date range of 1/27/20 - 1/31/20 is there. and no dates of 1/28/20 are anywhere to be found (which there was 1 in the smartsheet that had 1/28/20).

Now, this is how the actual smartsheet looks... Notice, the line for 'Salvador Carbajal (1/27/20)' is NOT checked off. But the line that is 'Amy Chap (1/28/20)' is checked off, and yet, did not make it to the report.

Any thoughts, anyone? Thank you so much, in advance, for any feedback.

Ella

@Milton Rodas The test on my end worked fine, so Paul was correct that the alert start date would not limit the workflow as I got tasks dated 1/24 and 1/23 and 1/28 (today) as long as the check box was checked.

Please provide details/screenshots of your alert set up.

Milton Rodas

Hi @Ella -- at what time in the day are you running your workflow? I have it set to 5 AM. Do you think the time influences the output?

Here's my alert setup, per your request:

Ella

Thanks @Milton Rodas. I had one set for 9 am and another one for 10 am. Both worked great.

Your alert set up is like mine so the time is the only difference. I will set up one for tomorrow at 5 am to see what happens.

@Paul Newcome , @Andrée Starå , do you have any other thoughts on this?

Paul Newcome

@Ella and @Milton Rodas

I feel that the issue may be arising from using the TODAY() function. The TODAY() function will only update when the sheet is activated (cross sheet reference/cell link update/form entry/update request/opening the sheet).

The automation pulled what was checked yesterday because at the time of the trigger, the TODAY() function hadn't been updated from yesterday's date yet. When you opened the sheet to look, it updated the TODAY() function and made it appear as if something had gone wrong.

So we need to figure out a way for the sheet to become "activated" BEFORE the trigger so that the TODAY() function stays current.

I personally use Zapier couple with cell linking for this. Basically I have a sheet called "zzzTODAY" (the z's make it easier to find). This sheet has a date column that simply has =TODAY() in it. Then I set up Zapier to add a new row everyday just after midnight. Zapier automatically adding the row updates the TODAY() function, then all of my sheets where I need TODAY(), I just cell link from zzzTODAY to whatever sheet I want the reference in then replace my TODAY() functions with referencing that new cell.

This ensures a constantly updating TODAY() reference even if the sheet stays closed for days, weeks, months, years...

Ella

This issue is with the TODAY formula as my mock alert was incorrect today since I have not been into the sheet yesterday, so this solution unfortunately won't work for @Milton Rodas.

Paul Newcome

@Ella

That is correct. The issue with the TODAY function as well as a possible solution using Zapier is in my post above.

Milton Rodas

Interestingly, today the Notification at 5 AM included every instance where the date had a range of TODAY (1/30/20). I think it is a bug by Smartsheet and think the Formula is good.

Basically, if today started at 12:01 AM and my notification is going out at 5 AM, it should include instances that have TODAY's date in it. Very simple. And yet, sometimes it catches the instances (like today) and sometimes it doesn't.

I am continuing to test.

Paul Newcome

@Milton Rodas I do think it has to do with the TODAY function and when the sheet is activated relative to when the notification is sent.

Here is a way to test it. If you view a PUBLISHED version of a sheet, it will not update the TODAY function.

Publish the sheet as Read Only. Tomorrow, do NOT open the sheet itself. Instead go to the published URL. Compare this to the data received in your email and see if it matches up.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.