Progressive Reminder Date Reminder until task is updated

rbarnes15506

I have a reminder set to notify a person 7 days before a calibration date.  Is there any way to have the reminder sent every day until the calibration date is updated with a new one?

Shaine Greenwood

Hello,

To accomplish this, you'll want to create multiple reminders—one 6 days before, 5 days before, etc. until you've got one for every day leading up to your calibration date.

rbarnes15506

That is what I was thinking, but wanted to make sure there wasn't an easier way.

Thank you Shaine.

J. Craig Williams

There is.

The exact setup requires knowing your collaboration situation. If you have global access and usage of the Sheet, it gets a bit trickier. You need to know when the sheet is accessed, because any 'touch' of the Sheet will trigger a refresh. Since the solution below requires the TODAY() function, that becomes important.

What follows is easier than it appears. It is one of those solutions that is easy to implement, but was not obvious to solve.

1. Set up a [Reminders Date] column.

You will want one for each "type" of Reminder, if there are more than one.

2. Build a formula in the [Reminder Date] based on [Calibration Date].

Make sure the [Calibration Date] is empty when you want it stop or not appear.

For example, this formula will return a date when all of these are true

• [ChkBox] is unchecked
• [Calibration Date] is not in the past.
• [Calibration Date] is not farther than 7 days in the future

=IF(AND([Calibration Date]8 - TODAY() <= 7, [Calibration Date]8 >= TODAY(), NOT(ChkBox8)), TODAY(), 0)

This formula may need to be tweaked to add or subtract a day (depending on your situation)

3. Set up your Reminder for either "on Reminder Date" or "1 day after Reminder Date" (again depending on your exact situation)

4. Set up some mechanism to 'touch' the Sheet to force a refresh. For small numbers of sheets, I use Zapier, otherwise, I build an API solution. See this post for more.

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

UPDATE: 2017-0917 - my solution has changed slightly and the post updated to reflect new method.

When you had the sheet 'touched' is important.

All of this is due to two features of a Reminder:

• It is not possible to specify the time of day that a reminder is sent.
• Reminders are sent between 12:00 AM and 4:00 AM based on the sheet owner’s time zone. For this reason, in most cases, you won’t receive a reminder on the same day that you create it. If you want to ensure that you receive a new reminder, choose a start date that occurs in the future.

But, by forcing the TODAY() formula's to recalculate, we know what the dates will be between 12am and 4am and can build the formula accordingly.

I hope this helps.

Craig