'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create a SharePoint Calculated Column for Due and Overdue Status
Question details
The user needs to set up a SharePoint calculated column that displays status tags (Due, Not Due, Overdue) based on a specific review date.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Managing task deadlines or document review cycles in a SharePoint list or library.
- Observed behavior
- Folders or items without a date remain blank, while items with a date accurately reflect their timeline status relative to the current date.
Ensure you have the necessary List or Site Administrator permissions to create and edit columns in your SharePoint site. Verify the exact name of your date column (e.g., 'Review Date') before applying any formulas.
Apply a Nested IF Formula for Status Tracking
Use a calculated formula that evaluates the 'Review Date' against the current date to determine if an item is Due, Overdue, or Not Due.
SharePoint calculated columns allow you to use Excel-like formulas to process data from other columns. By nesting IF statements with the TODAY() function, you can automatically categorize items based on their upcoming or missed deadlines.
Navigate to your SharePoint list or library. Click 'Add column', select 'More...', and choose 'Calculated (calculation based on other columns)'.
Name your column (e.g., 'Status') and paste the following formula into the formula box: =IF([Review Date]="","",IF([Review Date]<TODAY(),"Overdue",IF([Review Date]-7<TODAY(),"Due","Not Due")))
Ensure the 'Data type returned from this formula' setting is configured to 'Single line of text'.
Click 'OK' to save the column. Test the formula by adding or modifying items with various review dates to verify the correct status displays.
Create and Edit SharePoint Documents with WPS Office
While you manage your document status and deadlines in SharePoint, you can use WPS Office as a free, lightweight, and highly compatible alternative to Microsoft Office for creating, editing, and collaborating on your essential files.
- 1. Download WPS Office: Download and install WPS Office Free on your computer or mobile device.
- 2. Access SharePoint Files: Sync your SharePoint folder locally using OneDrive or download the specific documents you need to work on.
- 3. Edit Seamlessly: Open your .docx, .xlsx, or .pptx files natively in WPS Office to edit them without any formatting or compatibility issues.
Frequently Asked Questions
Why is my SharePoint calculated column returning a syntax error?
This usually happens if the column names in your formula do not perfectly match your existing list columns, or if your site's regional settings require semicolons (;) instead of commas (,) in formulas.
Can I highlight the 'Overdue' status in red?
Yes. Once the calculated column is created, you can use SharePoint's 'Format this column' feature to apply conditional formatting, adding background or text colors based on the text values (Overdue, Due, Not Due).
Does the TODAY() function update automatically every day in SharePoint?
No, the TODAY() function in SharePoint calculated columns only updates when the item itself is created or modified. For dynamic daily updates without editing items, it is recommended to use Power Automate to run a daily status check.
