Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Automatic reminders based on dates
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?
Comments
-
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?
-
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
-
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 point4) 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.
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives