How to Calculate the Next Review Date in SharePoint with Formulas
Question details
The user needs a nested IF formula for a SharePoint calculated column to automatically assign the next document review date based on selected intervals such as Annually, Semi Annual, or Quarterly.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Configuring a SharePoint list or document library to automatically calculate the next deadline based on a defined document review period.
- Observed behavior
- Expanding a single-condition IF statement into a nested IF statement to handle multiple interval lengths dynamically.
Ensure you have Site Owner or List Administrator permissions to create columns in your SharePoint library, and verify that the 'Document Review Period' and 'Last Reviewed' columns already exist.
Use a Nested IF Formula in a Calculated Column
Create a calculated date column that uses a nested IF function to add the correct number of days to the last reviewed date based on the chosen interval.
By nesting multiple IF statements, you can instruct SharePoint to evaluate the review period choice and output a specific number of days. Adding this result to your base date outputs the exact deadline.
Open your SharePoint List or Document Library. Click the gear icon in the top right corner and select 'List settings' or 'Library settings'.
Scroll down to the Columns section and click 'Create column'. Enter 'Next Review Date' as the column name.
Under the column types, choose the radio button for 'Calculated (calculation based on other columns)'.
In the Formula box, input the following calculation to add the interval days to your base date: =[Last Reviewed] + IF([Document Review Period]="Annually",365,IF([Document Review Period]="Semi Annual",189,IF([Document Review Period]="Quarterly",92,"")))
In the 'The data type returned from this formula is' section, choose 'Date and Time'. Select 'Date Only' for the format, then click 'OK' to save.
Manage Tracking Lists and Formulas Offline with WPS Office
If you are managing document tracking lists offline or prefer working in a standalone desktop environment, WPS Spreadsheet is a powerful, free alternative to Microsoft Office. It fully supports standard IF and date calculation formulas.
- 1. Export Your SharePoint List: Click 'Export to Excel' in your SharePoint list to download your current document tracking data as a compatible file.
- 2. Open with WPS Spreadsheet: Launch WPS Office and open the downloaded .xlsx or .iqy file to load your review period data locally.
- 3. Apply the Same Formula: Use the exact same nested IF logic in WPS Spreadsheet by replacing the bracketed SharePoint column names with cell references (e.g., =A2+IF(B2="Annually",365,IF(B2="Quarterly",92,0))).
Frequently Asked Questions
Why is my SharePoint calculated column returning a #Name? error?
This error usually occurs if the column names referenced in your nested IF formula are misspelled, have been deleted, or do not exactly match the actual column names in your SharePoint list.
How do I format the resulting calculation as an actual date instead of a number?
When creating or modifying your calculated column in SharePoint settings, look under the 'The data type returned from this formula is' section and change the selection from 'Number' or 'Single line of text' to 'Date and Time'.
Can I use leap year logic inside this SharePoint formula?
Adding exactly 365 days does not account for leap years. For precise date adjustments across years, you should use the DATE, YEAR, MONTH, and DAY functions combined to dynamically advance the calendar date.
Does this formula work the same way in Excel or WPS Spreadsheet?
Yes, the logical syntax for nested IF statements is identical in SharePoint, Microsoft Excel, and WPS Spreadsheet. You simply replace the SharePoint bracketed field names with standard spreadsheet cell references like A2 or B5.
