I have created a table which calculates a date using two fields.
The three fields are
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?
The three fields are
- Start date (Date field)
- Recurrence (Choice: Month, Year, 3 years)
- Next recurrence (Calculated column with date result)
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?