Automatic reminders based on dates

Ian Smith 2017

Hi All,

very new to smartsheet so please be kind

I have a requirement whereby I need to badger suppliers to ensure that they will deliver on schedule, so I'm hoping to do the following:

Add a column for expected delivery date 

Add a tickbox for "will require chasing"

Have a reminder automatically set to trigger at 50% of delivery date - entry date, so for example if delivery is say 3 months, then to send a reminder that it will need chasing after 6 weeks from the day the order is added to the smart sheet.

 

The addition of the columns is straight forward, and I can see how to set a manual reminder, but I can't find anything that helps me automate it dependent upon the delivery date.  Can anyone point me in the right direction please?

Ian Smith 2017

my thought processes are:

• =Networkdays([date]1, [delivery due]1)   ##gives number of days between order added to system and delivery date
• Network days/2 ## gives 50% trigger value
• If Today = date+ network days/2 then send reminder

Can this be combined into a single cell? Or would multiple columns be required?

J. Craig Williams

I misread the question.

Ian Smith 2017,

You will likely want to use NESTED IF

Here's a Smartsheet blog post describing what that is

https://www.smartsheet.com/blog/support-tip-build-nested-IF

If you want a Reminder (date based Alert) and not a Notification (change based Alert), make sure your final 'chasing' column is a Date type.

I hope that gets you started.

Craig

 

Ian Smith 2017

Sample Usage

NETWORKDAYS([Due Date]4, [Due Date]5)

Syntax

NETWORKDAYS(start_date end_date [holidays])

start_date—The first date to be measured.

end_date—The last date to be measured.

holidays—[optional] The dates to exclude from the count.

1) So if I set up a column for the following, and call it delvery span=

NETWORKDAYS([Ordered date],[delivery due date])

This will give me the nuber of days between ordering and expected delivery

2) Then set up a column to get half this value, call it trigger point

=([delivery span]1, /2) 

this will give me the half way point in days between the order date and the delivery date

3) Then set up a column for the trigger date

= Ordered date + trigger point

4) Then a conditional state of if Trigger date = today then?

There must be a tidier way to do this than multiple columns, but I must confess it's baffling me at the moment.

J. Craig Williams

Ian,

Apologies. I misread your initial post.

For your most recent comment, steps 1 - 3 can be combined into a single Date column [Trigger Date]

=[Ordered Date]23 + (NETWORKDAYS([Ordered Date]23, [Delivery Due Date]23) / 2)

for row 23.

For (4), use a Reminder with the condition "on Trigger Date". The TODAY() is understood by the system.

I hope that helps.

Craig

R.Silber

I have a similar situation -- I want a series of reminders to go out at different stages of a project, and I've set up a "networkday before due date" column and a "netday after the due date" column that I use as the "trigger" for the update requests, etc. But I think that sometimes a reminder doesn't go out if the ultimate date that it should send is on a nonworkday and I don't happen to open the sheet that day. For instance, if I want a contractor to get an update request 1 day after she receives a project and that day happens to be saturday -- if I don't open the sheet, until Sunday, then the trigger never happens -- the column updates from zero to 2 and never hits "1" for the trigger. 

Am I understanding this right? Is there any way I can set it up differently? I'm going to be on vacation for a week and I don't want to have to remember to open, save, and close the sheet each day for my "automation" to operate. Thanks for any help or advice. -R

 

Dan Engerer

Unfortunately you're being ignored by the staff and also unfortunately, I don't think theyve come up with an automatic solution yet after all these years of asking