How to Calculate a Review Date One Year Later in SharePoint
Question details
The user needs to automatically calculate and set a review date that is exactly one year after the 'Date of Full Review' in a SharePoint list.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating or updating a SharePoint list where document or item reviews happen annually.
- Observed behavior
- The user wants the system to output a Date and Time format representing exactly one year from a specified reference date column.
Ensure you have the necessary permissions (Site Owner or List Contributor with design rights) to add or modify columns in your SharePoint list or library.
Use a Calculated Column Formula for Annual Review Dates
Add a calculated column using the DATE function to automatically add one year to your existing date column.
SharePoint allows you to create Calculated Columns that automatically generate data based on other columns in the same list. By using the DATE, YEAR, MONTH, and DAY functions, you can precisely offset any date by a specific amount of time.
Navigate to your SharePoint list or library settings. Click on 'Create column', give it a name (such as 'Next Review Date'), and choose 'Calculated (calculation based on other columns)' as the column type.
In the formula text box, input the following exact formula: =DATE(YEAR([Date of Full Review])+1,MONTH([Date of Full Review]),DAY([Date of Full Review])). Ensure the referenced column name perfectly matches the one in your list.
Scroll down below the formula box and set 'The data type returned from this formula is' to 'Date and Time'. Choose 'Date Only' for the format, then click 'OK' to save your new column.
Manage Exported SharePoint Data Easily with WPS Office
While SharePoint handles list management online, analyzing and formatting that data offline requires a robust spreadsheet tool. WPS Office is a highly compatible, free alternative to Microsoft Office that lets you manage exported SharePoint lists seamlessly.
- 1. Download and Install WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Export Data from SharePoint: In your SharePoint list, click 'Export to Excel' to save your list data to your local drive.
- 3. Open and Edit: Open the exported file using WPS Spreadsheet to manage your records, apply advanced data analysis, or build offline dashboards.
Frequently Asked Questions
Why is my SharePoint calculated date returning a syntax error?
Syntax errors usually occur if the referenced column name is misspelled or missing brackets. Ensure you use exact brackets, such as [Date of Full Review]. Additionally, check your SharePoint site's regional settings; some regions require semicolons (;) instead of commas (,) to separate arguments in formulas.
Can I calculate a date 6 months in the future instead of a year?
Yes. To add exactly 6 months instead of a year, modify the MONTH part of the formula and remove the +1 from the YEAR part: =DATE(YEAR([Date of Full Review]),MONTH([Date of Full Review])+6,DAY([Date of Full Review])).
Will the calculated column update automatically if the original date changes?
Yes. SharePoint calculated columns dynamically update their displayed values whenever the referenced item or date in the list is modified and saved.
