Recurring Notification Triggered by Date in Column

Bethany Garcia

I'd like to set up a notification to be sent every 10 days while a row is in a certain status on a sheet. I can have the date the row is set to that specific status captured in a column, but what I can't figure out is how to have a notification send every 10 days while the row stays in that specific status. When I change the automation logic to Custom to say to send every 10 days it only lets me choose to send it every 10 days from a hard coded date, but I need it to send every 10 days from a date that is captured in the sheet itself. Does anybody know how to do this?

Leibel S

@Bethany Garcia

You can set up another column that has a formula and determines based on the date whether this row needs a notification sent.

You would then set the automation to send a notification every day based on if this column you created above says that one is needed.

Dan W

You can set this up in automation. When date is reached, then "Custom" instead of "Run once"

You could then set a condition based on the status

Bethany Garcia

@Leibel Shuchat @Dan W "You can set up another column that has a formula and determines based on the date whether this row needs a notification sent." would work for the first notification that needs to be sent if the row is still in a specific status after 10 days, but what that doesn't give me how to get the notification to continue to be sent every 10 days, not every day. The custom recurrence doesn't work either because it will run ever 10 days from a static date, not a dynamic date unique to each row in the sheet. If I set a custom recurrence to run the notification every 10 days from today for example, but a row hits its 10th day and requires the notifiaction to be sent tomorrow, it wouldn't go out for another 9 days (resulting in the notification being sent after 19 days instead of 10 days).

I could definitely be completely missing the suggestion each of you are making though for this to work so please let me know if that's the case.

Leibel S

@Bethany Garcia

You would add another column showing last sent notification, your automation records a date in here whenever it sends a notification, you would then use this date to calculate if this row needs a notification or not. Example:

=IF(AND(Status@row = "Your Status", SUM(TODAY() - [Last Notification Date]@row) >= 10), 1, 0)

Toufong Vang

Hi, Bethany.

Here is another approach.

Assuming the column where you save the date when [Status] changes to green is labeled, [Date Status eq Green] ...

1) Create a helper column labeled [Count Days Diff] (or something of your preference).

2) Create workflow automation that:

• Triggers/Runs when [Status] is changed.

  • CONDITION 1... Where [Status] = "Green"

    -- ACTION "Record a date" in [Date Status eq Green]

 • OTHERWISE... ACTION is "Clear cell value" in [Date Status eq Green]

 3) Column formula in [Count Days Diff] ...

= MOD(IFERROR( NETDAYS([Date Status eq Green]@row, TODAY()), 1),10)

4) Create a workflow automation that:

 • Triggers/Runs everyday at a time of your choosing.

 • CONDITION 1... Where [Status] = "Green" and [Count Days Diff] = 0

 • ACTION... your alert.

EXPLANATION

#2 records a date in the column [Date Status eq Green] whenever [Status] is changed to "Green". It clears out [Date Status eq Green] when [Status] is changed to any other value.

#3 Finds the number of days elapsed between [Date Status eq Green] and today's date, divides it by 10, and returns the remainder (using MOD() function). If this doesn't error out, every 10 days the remainder will be "0". Should it error out, the formula returns "1" instead.

#4 runs everyday and sends an alert (or does whatever action you choose) if [Status] is "Green" AND another 10 days have gone by ([Count Days Diff] contains "0").