How to create a calculated date column for recurring dates?

[gable]

I have created a table which calculates a date using two fields.
The three fields are

1. Start date (Date field)
2. Recurrence (Choice: Month, Year, 3 years)
3. Next recurrence (Calculated column with date result)

The rule is If Recurrence is Month the Next recurrence will be Start date - 1 day and + 1 month (add 1 month after 06.17 23:59:59)
So 2022.06.17 will be 2022.07.16 and after it it will be 2022.08.16 ...
The same applies as years (add 1 year after 06.17 23:59:59)
2022.06.17 will be 2023.06.16 and after it it will be 2024.06.16 ...
and 3 years (add 3 years after 06.17 23:59:59)
2022.06.17 will be 2025.06.16 and after it it will be 2028.06.16 ...
I have wrote this formula for the calculated column:
=IF([Recurrence]="Month";
DATE(YEAR(TODAY());MONTH(TODAY());DAY([Start date])-1)+TIME(23;59;59);
IF([Recurrence]="Year";
DATE(YEAR(TODAY())+1;MONTH([Start date]);DAY([Start date])-1)+TIME(23;59;59);
DATE(YEAR([Stat date])+3;MONTH([Start date]);DAY([Start date])-1+TIME(23;59;59))))

But the problem is if the start date is 2022.06.17 and today is 2022.06.20 the today formula will return this month (06) and subtracts 1 day so the date will be 2022.06.17 but it should be 2022.07.17
Can someone help me to solve this with me?

 

[StephenCrump]

Welcome to the Forum!

Your rule says nothing about today's date. So it's not clear why your formula is using TODAY()?

Also, if your Start Date is, say 31 January 2022, and the increment is one month, what recurring dates do you want to show for Feb, March and April?

 

[gable]

Welcome to the Forum!

Your rule says nothing about today's date. So it's not clear why your formula is using TODAY()?

Also, if your Start Date is, say 31 January 2022, and the increment is one month, what recurring dates do you want to show for Feb, March and April?

Thank you for your reply,

The Today if for always showing the current month, but its wrong. It needs to be showing the next "billing" period.

The rule is basically to add the [Recurrence] and subtract -1 days from the [Start date]
If the date passed show the next recurrence.

 

[gable]

I have made adjustments for my formula

=IF([@Recurrence]="Month";
DATE(YEAR([@[Start date]]);MONTH([@[Start date]])+1;DAY([@[Start date]])-1);
IF([@Recurrence]="Year";
DATE(YEAR([@[Start date]])+1;MONTH([@[Start date]]);DAY([@[Start date]])-1);
IF([@Recurrence]="3 Years";
DATE(YEAR([@[Start date]])+3;MONTH([@[Start date]]);DAY([@[Start date]])-1))))

Recurrence​	Start date​	Next billing cycle​	TODAY​	Next billing cycle should be (Manual text)​
Month​	2022.01.10​	2022.02.09​	2022.06.24​	2022.07.09​
Year​	2022.01.10​	2023.01.09​	2022.06.24​	2023.01.09​
3 Years​	2022.01.10​	2025.01.09​	2022.06.24​	2025.01.09​