Sharepoint Calculated Date columns

[AndyC2235]

Hi All

I have 3 date columns in Sharepoint (Completion Date, Next Review, Last Review). I want to calculate the next review column and last review column so they are dynamic. The completion date should be static.

The next review date should be 3 years from the completion date but when that date is reached another 3 years should be added then the last review column should be populated with the previous date if that makes sense. I've got bits and pieces working so far... 

This adds 3 years to the Completion Date

DATE(YEAR([Completion Date])+3,MONTH([Completion Date]),DAY([Completion Date]))

This adds 3 years to the Next Review Date once Todays date is the same as the Next Review Date

IF(DATE(YEAR([Next Review]),MONTH([Next Review]),DAY([Next Review]))=TODAY(),DATE(YEAR([Next Review])+3,MONTH([Next Review]),DAY([Next Review])),"")

I just help with replacing the last review date with the previous next review date. Its probably something simple which I have overlooked. Any help much appreciated.

Thanks

[#2]

@AndyC2235 SharePoint will update the calculated columns at the same time and it won't therefore know what the previous next review date was. It would be much better & easier to do it with a flow in Power Automate where you can store the current value in a variable and then do the calculations in the flow and update the list item.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

[#3]

@Rob_Elliott Thank you. I'll do that instead.