Morning!
This is my first post! I have been trying to figure out how to create my annual reminder for different contract dates. Provided is just a snapshot, there are hundred with different start dates and lengths. I am looking to create and Annual Reminder that is 90 days before the annual anniversary date - however I can find a way to create the desired output below. When I try, I keep getting 90 days before the CONTRACT end date.
This will be used to keep track of when the next outreach is.
DESIRED OUTPUT:
Contract Start Date | Contract End Date | Annual Reminder |
2020-02-28 00:00:00 | 2021-02-27 00:00:00 | 2020-11-28 |
2019-04-09 00:00:00 | 2022-04-08 00:00:00 | 2021-01-09 |
2018-08-18 00:00:00 | 2023-08-17 00:00:00 | 2021-05-18 |
It may be a simple workflow, but any guidance is appreciated!
Solved! Go to Solution.
Hi @Madden1011
Didn't this work for the upcoming aniversary
DateTimeAdd(DateTimeAdd([Contract Start Date],1,"year"),-90,"days")
Start Date + 1 yr - 30 days
What were the issues ? And what's the expected calculation looking forward to help
If going off the Contract Start Date with the proposed calculation, some of them started say back in 2018, so the DateTimeAdd(DateTimeAdd([Contract Start Date],1,"year"),-90,"days") would put the reminder date at 2019, not the upcoming date. I have placed an example below of the outputs and what I would be looking for.
Contract Start Date: 2018-04-06 00:00:00
Using Proposed Solution: 2019-01-06 00:00:00
End Goal: 2021-01-06 (the next upcoming date)
Hi @Madden1011
By seeing the example. And you said upcoming.
Ok I think I get it now. You want to consider the current date and get the date of upcoming reminder.
Am I anywhere near the logic ?
Precisely. Based on the current date, when is my next reminder?
Hi @Madden1011
That's great now I have more visibility one more question.
I have ended my day and away from my system right now. But I will prep up a workflow as soon as possible.
No rush!
Hi @Madden1011 ,
I couldnt wait had to solve it.
Here is the workflow for the task.
Input
Output
1st Row Contract Date + 1yr -90 days was upcoming so thats correct.
2nd & 3rd Row Contract Date + 1yr -90 days had already passed so its giving the upcoming date.
Formula
IF DateTimeToday()>=[Contract Start Date]
THEN
DateTimeAdd(DateTimeAdd([Contract Start Date],DateTimeDiff(DateTimeToday(),[Contract Start Date],'year')+1,'year'),-90,'days')
ELSE
DateTimeAdd(DateTimeAdd([Contract Start Date],1,'year'),-90,'days')
ENDIF
Hope this helps and meets the criteria.
Looking forward to hearing back from you.
THAT IS EXACTLY IT!
Works for all my records, thank you so much! I really appreciate your help today 🙂 This is going to make my life a lot easier.
Thanks for clearing up the confusion on request.
For sake of an alternative solution to @atcodedog05 .
I'd suggest a 2 step calculation. First, work out when it is/was this year:
Left(DateTimeToday(),5) + Substring(DateTimeAdd([Contract Start Date],-90,"days"), 5, 5)
Then shift forward one year if needed:
DAteTimeAdd([Computed Reminder], IIF([Computed Reminder]<DateTimeToday(),12,0), "Months")