How to Fix a SharePoint Calculated Column That Shows Zero
Question details
The user needs to fix a SharePoint calculated column that incorrectly outputs a zero instead of the calculated number of days.

- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Calculating the number of days since an item was created using NOW, DATEVALUE, and TEXT functions in a SharePoint list.
- Observed behavior
- The calculated column unexpectedly displays a zero (0), and in some troubleshooting scenarios, it throws a #Name? error when formulas or data types are adjusted.
Ensure you have the necessary site owner or list administrator permissions in SharePoint to edit column settings and modify list formulas.
Change the Return Data Type to Single Line of Text
Modify the calculated column's output settings to return a text string instead of a number to resolve data type mismatches.
Often, SharePoint struggles to parse dates and complex combinations of TEXT and DATEVALUE functions into a pure Number format. Switching the output format forces SharePoint to evaluate the result as a text string, which typically bypasses the zero-value bug.
Open your SharePoint list, click on the Settings gear icon in the top right corner, and select 'List settings'.
Scroll down to the 'Columns' section and click on the name of the calculated column that is incorrectly displaying zero.
Under the section 'The data type returned from this formula is', select 'Single line of text' instead of 'Number'.
Click 'OK' at the bottom of the page to save the settings. Return to your list to ensure the correct number of days is now displayed.

Verify Formula Syntax and Item Updates
Ensure the formula using NOW() and DATEVALUE() is structured correctly and that the item itself has been updated to trigger a recalculation.
Manage Your Data Smoothly with WPS Office
While SharePoint handles list management online, managing complex formulas and spreadsheet data is often easier offline. WPS Spreadsheet provides a lightweight, highly compatible alternative to Microsoft Excel for building complex date calculations without unexpected zero-value errors.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite.
- 2. Open WPS Spreadsheet: Launch the application and create a new spreadsheet to manage and test your list data.
- 3. Use Date Functions: Easily calculate date differences using functions like DATEDIF or simple subtraction without strict format restrictions.

Frequently Asked Questions
Why does my SharePoint calculated column show #Name? after changing the data type?
The #Name? error usually occurs when the formula references a column name that does not exist, has been renamed, or contains a typo. Check your formula syntax in the List Settings to ensure all column names match exactly.
Are NOW() and TODAY() functions fully supported in SharePoint calculated columns?
No, Microsoft does not officially support volatile functions like NOW() and TODAY() in calculated columns. They only calculate when the item is created or updated, meaning the 'days since created' will not update automatically every day unless the item is edited.
Does switching from Classic to Modern view fix calculation errors?
No, switching between Classic and Modern SharePoint views only changes the user interface presentation. It does not affect the underlying data processing or resolve calculation formula errors.




