Others

How to Fix a SharePoint Calculated Column That Shows Zero

Amos GikundaAmos Gikunda Nov 3, 2026 870 views

Question details

The user needs to fix a SharePoint calculated column that incorrectly outputs a zero instead of the calculated number of days.

How to Fix a SharePoint Calculated Column That Shows Zero
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.
Before you start

Ensure you have the necessary site owner or list administrator permissions in SharePoint to edit column settings and modify list formulas.

Solution 1Recommended

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.

1
Navigate to List Settings

Open your SharePoint list, click on the Settings gear icon in the top right corner, and select 'List settings'.

2
Edit the Calculated Column

Scroll down to the 'Columns' section and click on the name of the calculated column that is incorrectly displaying zero.

3
Change the Data Type

Under the section 'The data type returned from this formula is', select 'Single line of text' instead of 'Number'.

4
Save the Changes

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.

Change the Return Data Type to Single Line of Text
Check for Subsequent Errors: If the field later displays a '#Name?' error, double-check your formula syntax to ensure all referenced columns are spelled correctly.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite.
  2. 2. Open WPS Spreadsheet: Launch the application and create a new spreadsheet to manage and test your list data.
  3. 3. Use Date Functions: Easily calculate date differences using functions like DATEDIF or simple subtraction without strict format restrictions.
Free, lightweight, and fast to installHighly compatible with Microsoft Excel (.xlsx) formatsFamiliar interface for managing complex date formulas natively
microsoft office alternative - wps office

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.