If formula for recurring date based on date value of another cell

EgliAlv

Hi everyone!

I would like a formula to automatically update a date column every 3 years based on another Date column

E.g. If reference cell is 1-SEP-2022 then I want the next Date column cell to automatically change to 1-SEP-2025, then to 1-SEP-2028, and so on…

Does anyone know if this is possible?

Thank you,

Andrée Starå

Hi @EgliAlv

I hope you're well and safe!

Try something like this.

=DATE(YEAR([Reference Column]@row) + 3, MONTH([Reference Column]@row), DAY([Reference Column]@row))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

EgliAlv

Hello Andree,

Thanks for a quick reply. I get invalid result. Once this works, it will get trickier because I want the renewal date to be updated every 3 years. Please, see below, kindly let me know

Andrée Starå

@EgliAlv

Happy to help!

Can you double-check the Renewal Date and that it's a Date Column?

Regarding the update every three years. As long as you change the Issue Date, the Renewal will update.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

EgliAlv

Thanks a lot, Andree. Both the Issue and Renewal columns are Date Column ; even if I try a basic formula =MONTH it doesn’t work, I get error #DATE EXPECTED

unless that I change the Renewal to Text Column.

About the recurring date challenge, it’s important to note that Issue Date will be fixed, it never changes, therefore the Renewal date should update every 3 years based on that Issue Date, do I make sense?

Genevieve P.

Hi @EgliAlv

How is your Issue Date column data being entered in to the cell, is it by a formula or a manual date selection?

The formula Andrée provided will error if the cell you're referencing contains text. Can you try this formula to test if the data is read as a date or not?

=IF(ISDATE([Issue Date]@row), "Yes - Date", "No - Text")