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?

Find more posts tagged with

Services

Comments

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