0

I have created a SharePoint list 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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.