Formula for sending report after 1 year, considering previous and future dates

[FrankLovesAirta]

Hello community,

I already searched through the forum and found a helpful formula - with a minor problem based on that formula that I am trying to fix. Our datetime_format is D-M-YYYY.

Background:
We have a field called {start date} for when a new client relationship starts. Let’s say one client started on 1st September 2022 (past date) and another client will start on 1st November 2022 (future date). We have a report automation that we want to send out every year. The first report will be sent out one year after the client was onboarded, that process starting now. That means we do not want to send out multiple reports for a client that started on 1st July 2010, instead they will receive their first report on 1st July 2023. In our understanding we need a date formula adding one year to the {start date} and also consider the current year.

Formula:
IF({start date}, DATETIME_PARSE(DAY({start date}) & “-” & MONTH({start date}) & “-” & (YEAR(NOW()) + IF(DATETIME_FORMAT(DATEADD({start date}, 1, “days”), “MMDD”) > DATETIME_FORMAT(NOW(),“MMDD”),0, 1)), “D-M-YYYY”))

Problem:
For our first example (1.9.2022) the result (1.9.2023) is what we want. For the second example (1.11.2022) the result is (1.11.2022) not what we want. This would lead to a client receiving a report immediately after their start date and not one year after it.

Thank you all, your help is much appreciated.

edit: sorry, fixed some typos in my initial post

[Mohamed_Swella1 · Accepted solution]

Hi @FrankLovesAirtable

You need to edit your formula as follows:

IF({start date}, 


IF(YEAR(TODAY())=YEAR({start date}),

DATEADD({start date},1,"years")

,

DATETIME_PARSE(DAY({start date}) & "-" & MONTH({start date}) & "-" & (YEAR(NOW()) + IF(DATETIME_FORMAT(DATEADD({start date}, 1, "days"), "MMDD") > DATETIME_FORMAT(NOW(),"MMDD"),0, 1)), "D-M-YYYY")))

I didn’t edit your original formula although I don’t see why you are parsing the {start date} field if it is already a Date field time.

[FrankLovesAirta]

Thanks a lot, @Mohamed_Swellam. This works like charm and I now understand how easy the solution would have been with just combining it with another IF statement. Have a great day :slightly_smiling_face: TAGGED AS SOLUTION