Creating Annual Anniversary Date

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!

Workflow

Date Time

Accepted answers

atcodedog05

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.

Annual.yxmd

All comments

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.

Reminder.yxmd

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.

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.

Annual.yxmd

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

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

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.

Annual.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703