How to Create a SharePoint Calculated Review Date from a Review Period
Question details
The user needs to automatically generate a document review date in a SharePoint list by adding a specific number of years (Review Period) to an existing Issue Date.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Managing document lifecycles and compliance tracking in a SharePoint document library or list.
- Observed behavior
- The user wants a calculated column that evaluates the issue date and review period to automatically determine the future review date.
Ensure you have 'Manage Lists' permissions in your SharePoint site. Verify that your existing 'Issue Date' column is configured as a Date/Time type and your 'Review Period' column is configured as a Number type before setting up the formula.
Create a Calculated Column using Date Formula
Use a SharePoint calculated formula to accurately extract and add years to the base issue date.
SharePoint allows you to create calculated columns that perform mathematical operations on other columns within the same item. By using the DATE, YEAR, MONTH, and DAY functions, you can specifically target the year value and add your review period integer to it without affecting the month and day.
Open your SharePoint list or document library. Click on the gear icon (Settings) in the top right corner and select 'List settings' or 'Library settings'.
Scroll down to the Columns section and click the 'Create column' link.
Enter 'Review Date' as the Column name. Under the type of information, select 'Calculated (calculation based on other columns)'.
In the Formula box, enter the following formula to add the years: =DATE(YEAR([Issue Date])+[Review Period Number], MONTH([Issue Date]), DAY([Issue Date])). Ensure the column names in brackets exactly match your existing columns.
Under 'The data type returned from this formula is', select 'Date and Time'. Choose 'Date Only' for the format, and click OK to save your new column.
Manage Your Documents Effectively with WPS Office
While SharePoint handles complex list calculations and server-side document management, you can seamlessly open, edit, and collaborate on those downloaded SharePoint files using WPS Office. It provides a lightweight, free alternative to Microsoft Office that is fully compatible with standard document formats.
- 1. Download WPS Office: Visit the official WPS website to download and install WPS Office for free on your device.
- 2. Download files from SharePoint: Select the required documents from your SharePoint library and download them to your local device.
- 3. Edit with full compatibility: Open the downloaded documents in WPS Office. You can edit, format, and save them with zero compatibility issues before uploading them back to SharePoint.
Frequently Asked Questions
Why is my SharePoint calculated date returning a #VALUE! error?
This usually happens if the columns referenced in your formula (like Issue Date or Review Period) are empty for a particular row, or if they are formatted as a Text type instead of Date/Time and Number types. You can wrap your formula in an IF statement to check for blank values, such as =IF(ISBLANK([Issue Date]),"",DATE(...)).
How do I add months instead of years to a SharePoint date column?
To add months, you need to adjust your formula to add the value to the month parameter rather than the year parameter. For example: =DATE(YEAR([Issue Date]), MONTH([Issue Date])+[Review Months], DAY([Issue Date])).
Can I automatically email users when the calculated review date approaches?
Yes, you can use Power Automate (formerly Microsoft Flow) to create a scheduled cloud flow. You can configure it to check the 'Review Date' column daily and send an email alert to the document owner when the date is within a specific timeframe.
Can I use the calculated review date to trigger a SharePoint retention policy?
Yes, Microsoft Purview allows you to create retention labels that trigger based on specific date columns, including calculated columns like your custom Review Date, helping you automate compliance and document lifecycle management.
