Alert recurrence that looks at a date field - possible?

Melisa Dannhauser

I'm trying to set a recurring alert workflow to remind someone that they need to approve a submission. I have a "submission date" field and I would like a workflow that sends a reminder one week after submission date is reached and then weekly after that, as long as the status field is not changed to "approved".

I was able to set an alert to occur only ONCE, one week after the "submission date" field is reached.

I was also able to set a recurring WEEKLY alert, but I don't see an option to set the start date as the date located in the "submission date" field. The only recurring option I see is to send the reminder weekly, starting on a set date I have to enter from a calendar, which is not feasible as all of my rows will have different submission dates.

Any input on how to achieve this would be appreciated. I really don't want to set 20+ different workflows for each week...

Paul Newcome

Insert a date type column which can be used to trigger the alert. Have the alert set to run on this date column using this formula:

=IF(Status@row <> "Approved", [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row) * 7))

The meat of this formula takes the current week number and subtracts the Submission Date week number from it to tell us how many weeks in the past the Submission Date is. We then multiply that by 7 to get the number of days between the Submission Date and the same day of the week for the current week and add it to the Submission Date.

Finally we wrap that in an IF to only populate a date if the Status is not "Approved".

Melisa Dannhauser

@Paul Newcome Got it to work but when it's a current submission and the week difference is 0, this formula populates the submission date. I don't want that to be the trigger, it needs to be a week after...

Heather Duff

@Melisa Dannhauser try moving one of the parenthesis at the end:

=IF(Status@row <> "Approved", [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7)

Melisa Dannhauser

@Paul Newcome Yup, figured that out and got it to work. But now, when it's a current submission and the week difference is 0, this formula populates the submission date. I don't want that to be the trigger, it needs to be a week after...

Paul Newcome

@Heather D Thanks for catching that.

@Melisa Dannhauser Sorry about that parenthesis mix-up. Ugh. Those things can be such a pain sometimes. Let's give this a try...

=IF(AND(WEEKNUMBER([Submission Date]@row) <> WEEKNUMBER(TODAY()), Status@row <> "Approved"), [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7)

Melisa Dannhauser

That works perfectly, thank you! I had accomplished the same with a more complicated formula so I always appreciate a shortcut!! This was mine:

=IF(IF([Status]@row <> "Approved", [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7), "") = [Submission Date]@row, "", IF([Status]@row <> "Approved", [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7))

Paul Newcome

Happy to help. 👍️

Roshnee Pradhan

Hi,

Can I please know what value does this IF function show? I have similar issue. I have a target date and I want to notify when the target date is reached and weekly after that until the status is changed.

Thank you,

Roshnee

Paul Newcome

@Roshnee Pradhan The IF formula above will output a date 1 week out from the Submission Date.

Melisa Dannhauser

@Paul Newcome I know this was a while ago, I have a similar request to my original one but instead of the notification going out every 7 days, I need it to go out ever 3 days until the status changes. Same exact formula above but needs to be 3 days apart... Any input? I tried modifying the formula above but it's not working correctly... TIA!

Paul Newcome

Try this and let me know how it works... If it does work how you need, then I wonder if you could replace the 3 with a 7 to further simplify the original formula.

=IF(Status@row <> "Approved", TODAY(MOD(TODAY() - [Date Column]@row, 3)))

Melisa Dannhauser

It works, but not every time... Sometimes it calculates 3 days, sometimes 4... Also, if I have a future date in the Date column, it doesn't know what to do so I will nee to add another part that says if date is larger than today() then do nothing. Which is fine. I'm just not sure how the formula works so I can't figure out if I can adjust it. If my submission date is 5/30, then I get a correct date with this formula, today. But if it's 5/31, the formula produces two days from today (instead of tomorrow...). Also, it would be nice to ONLY count working days (no SAT/SUN) but not sure how much more complicated that gets...

I can't believe this is so hard, why can't this just be a workflow that says "every 3 days" starting on a date in a column... Any more input is appreciated.

Paul Newcome

I don't know why I didn't think of the CEILING function before. Try this (also incorporates workingdays)...

=IF(AND(Status@row <> "Approved", [Date Column]@row < TODAY()), CEILING(NETWORKDAYS([SDate Column]@row, TODAY()), 3)

Melisa Dannhauser

@Paul Newcome Hmmm, I get "date expected" - is this not producing a date? Also, I assume the S in this part of the formula is a typo, right? [SDate Column]

Paul Newcome

You are correct that the "S" was a typo sorry about that. And the error was also my fault. I got a little ahead of myself. Try this one...

=IF(AND(Status@row <> "Approved", [Date Column]@row < TODAY()), [Date Column]@row + CEILING(NETWORKDAYS([Date Column]@row, TODAY()), 3)