How to Calculate a SharePoint Review Date Based on Criticality
Question details
The user needs to automatically calculate a future 'Review Date' for documents in a SharePoint library based on a 'Date of Issue' and a 'Criticality' choice field that contains a mix of numbers and text.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Managing a document control library where different criticality levels require different review intervals (e.g., 1, 2, 3, or 5 years) to maintain compliance.
- Observed behavior
- The calculated formula fails when referencing the Criticality column because the choice field contains additional text (e.g., '1 - Critical') rather than just a numeric value.
Ensure you have 'Design' or 'Full Control' permissions in your SharePoint Document Library to create and manage calculated columns.
Use a Nested IF Formula with Exact Text Matching
Create a Calculated Column that adds years to the Date of Issue using DATE, YEAR, MONTH, and DAY functions, while matching the exact text string of your Criticality choice field.
SharePoint calculated columns evaluate text exactly as it is stored. If your Criticality choice field contains text like '1 - Critical' instead of just '1', your IF statement must include the entire exact string. Otherwise, the formula will not recognize the value.
Navigate to your SharePoint Document Library. Click on the gear icon (Settings) and select 'Library settings'. Scroll down and click 'Create column'. Name the column 'Review Date' and select 'Calculated (calculation based on other columns)' as the column type.
Verify the exact text used in your Criticality choice column. For example, note if it uses spaces, hyphens, or specific casing like '1 - Critical', '2 - High', '3 - Medium', etc.
In the Formula box, construct a nested IF statement comparing the exact text. For example: =IF([Criticality]="1 - Critical", DATE(YEAR([Date of Issue])+2, MONTH([Date of Issue]), DAY([Date of Issue])), IF([Criticality]="2 - High", DATE(YEAR([Date of Issue])+3, MONTH([Date of Issue]), DAY([Date of Issue])), [Date of Issue]))
Under 'The data type returned from this formula is', select 'Date and Time', and choose 'Date Only' for the format. Click 'OK' to save the column and verify the results in your library.
Edit SharePoint Documents Seamlessly with WPS Office
While SharePoint natively handles your document metadata and calculated review dates, viewing and editing the actual Word, Excel, or PDF files requires a robust office suite. WPS Office is a highly compatible, lightweight, and free alternative to Microsoft Office that handles all your standard document control formats.
- 1. Download Document: Select the document in your SharePoint library and download it to your local device.
- 2. Open with WPS: Right-click the downloaded file and select 'Open with WPS Office' to launch it in a highly compatible editing environment.
- 3. Edit and Re-upload: Make your necessary revisions, save the file, and upload it back to SharePoint as a new version.
Frequently Asked Questions
Why does my calculated Review Date column show a #VALUE! error?
This error typically occurs when the text inside your formula does not perfectly match the text in your Criticality choice field (e.g., missing a space or typo), or if the 'Date of Issue' field is blank. Wrapping your formula in an IF(ISBLANK([Date of Issue]),"",...) can prevent errors on empty rows.
Can I extract just the number from the Criticality text for the formula?
Yes, if the number is always the first character, you can use the LEFT function, such as LEFT([Criticality],1), to extract the number and use it in your IF statements instead of typing out the full text string.
Will the calculated Review Date update automatically?
Yes, calculated columns in SharePoint automatically recalculate and update their values whenever the item is edited and the underlying fields (Date of Issue or Criticality) are changed.
