Alert recurrence that looks at a date field - possible?

05/04/21
Answered - Pending Review

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

Previous1

Answers

  • Paul NewcomePaul 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([email protected] <> "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".

    thinkspi.com

  • edited 05/04/21

    @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 DHeather D ✭✭✭✭✭

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

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

  • @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 NewcomePaul 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()), [email protected] <> "Approved"), [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7)

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • @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 NewcomePaul 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([email protected] <> "Approved", TODAY(MOD(TODAY() - [Date Column]@row, 3)))

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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

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

    thinkspi.com

  • @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 NewcomePaul 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([email protected] <> "Approved", [Date Column]@row < TODAY()), [Date Column]@row + CEILING(NETWORKDAYS([Date Column]@row, TODAY()), 3)

    thinkspi.com

Sign In or Register to comment.