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:

DATE(YEAR(TODAY());MONTH(TODAY());DAY([Start date])-1)+TIME(23;59;59); 
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?


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.