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!

[MarqueeCrew]

@Madden1011 ,

did you try putting:

datetimeadd([contract end date],-90,"days")

 into a date field with a formula tool?

 Cheers,

 mark

[jdunkerley79]

Hi,

Welcome to the community.

A formula tool with an expression of:

DateTimeAdd([Contract End Date],-89,"days")

should do what you need

Remember to  choose add Column in top left to create a new column.

Quick sample attached.

[Madden1011]

I have but it gives me something along the lines of...

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	2023-05-18

Whereas I would be looking for 2021-05-18 and not 2023-05-18

[atcodedog05]

Hi @Madden1011 ,

If i understand right you want Annual reminder (90 days before) based on Contract Start Date irrespective of end date

DateTimeAdd(DateTimeAdd([Contract Start Date],1,"year"),-90,"days")

It adds a year on start date and minuses 90 days.

Hope this helps : )

If this helps please mark the post as solution.

[Madden1011]

I have attempted the DateTimeAdd function, but it simply removed the days from the contract end date.

If I have a contract that is three years long, it will give me a reminder date of 90 days before that end date, rather than an annual anniversary reminder of the contract start date. Say the contract starts in 2018 and ends in 2023, we would want to reach out every year in between on the 90 days before the anniversary rather than just 90 days before the contract ends.

Hope that makes sense.

[jdunkerley79]

I think:

DateTimeAdd(
    [Contract Start Date],
    Ceil(DateTimeDiff([Contract End Date],[Contract Start Date],"days")/365)*12-3,
    "months")

does what you need

Works out number of years and then knocks 3 months off the end. This aligns with the start date anniversary.

Sample attached

[Madden1011]

This is sooo close! It does exactly what I want, however for those with Contract Start Dates in 2018 it provides 2019 as anniversary date. Whereas I would want it to show 2020-11-27

Example:

Contract Start Date	Contract End Date	Annual Reminder
2019-02-25 00:00:00	2021-02-24 00:00:00	2019-11-27 00:00:00

[atcodedog05]

Hi @Madden1011 ,

I think this what you are looking out for.

Input

Output

Second contract had duration of 3 years. And this generates reminder on 1 year anniversary - 90 days every year

Workflow

Hope this helps : )

If any issues please reach we can calibrate.

If this helps please mark the post as solution.

[Madden1011]

Thanks for helping me along with this!

The issue with the proposed workflow is that it created hundreds upon hundreds of additional rows for what I am working with.

Maybe instead of annual anniversary reminder I referred to it as "Upcoming Reminder", that looks at the next reminder. 

I am planning on attaching this workflow to another than runs quite often, so if one reminder date passes it would be ideal that it would get replaced with the next upcoming reminder when the workflow is run again. 

Apologies if I am making this super complex or unclear