Creating Annual Anniversary Date

[Madden1011]

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!

[atcodedog05]

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

[Madden1011]

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)

[atcodedog05]

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 ?

[Madden1011]

Precisely. Based on the current date, when is my next reminder?

[atcodedog05]

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.

[Madden1011]

No rush! 

[atcodedog05 · Accepted solution]

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.

[Madden1011]

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.

[atcodedog05]

Happy to help : ) @Madden1011 

Cheers and Happy analyzing : )

[jdunkerley79]

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")