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

Ian Smith 2017
Ian Smith 2017 ✭✭✭
edited 07/31/17 in Archived 2017 Posts

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?

Tags:

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?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 08/05/17

    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 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
    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
    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

     

  • 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

This discussion has been closed.