SharePoint Document Issues

Fix SharePoint JSON Column Formatting Showing NaN for Days Since Created

Bushra Parveen Sep 28, 2026 868 views

Question details

The user is experiencing a 'NaN' output when trying to calculate and display the number of days since an item was created using SharePoint JSON column formatting.

How to Fix SharePoint JSON Column Formatting Showing NaN

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Creating a custom JSON column format to calculate the days elapsed since document or list item creation.
Observed behavior: The JSON column returns 'NaN' (Not a Number) instead of the expected day count, primarily caused by using a calculated column for the 'Created' date.

Before you start

Ensure you have list management or site owner permissions in SharePoint to edit column formatting and access advanced list settings.

Solution 1Recommended

Reference the System 'Created' Field Directly in JSON

The most common cause of the NaN error is using a calculated column as the source for date calculations in JSON. Bypassing the calculated column resolves this.

SharePoint JSON formatting struggles to parse date values originating from calculated columns (e.g., formula =Created). To perform mathematical date operations correctly, you must reference the internal system date fields directly in your JSON payload.

Open Column Formatting

Navigate to your SharePoint list, click the column header for your custom JSON column, select 'Column settings', and then click 'Format this column'.

Edit the JSON Code

In the formatting pane on the right side of the screen, click 'Advanced mode' at the bottom to view the raw JSON code.

Update Date Reference

Locate the date calculation in your JSON. Replace the reference to your calculated column (such as '[$MyCalculatedDate]') with the internal system field '[$Created]'.

Save and Verify

Click 'Save' at the bottom of the pane. Refresh the page to verify the column now displays the correct number of days instead of NaN.

Reference the System 'Created' Field Directly in JSON

Supported Date Expressions: Ensure you are using supported column-formatting expressions, such as subtracting '[$Created]' from '@now', to calculate differences from the current date.

Solution 2

Seek Assistance in the SharePoint Developer Community

If directly referencing the Created field doesn't fix the NaN output, your JSON syntax might have unsupported date expressions requiring developer input.

Free Microsoft Office alternative

Try WPS Office for Seamless Document Management

While SharePoint requires complex JSON for custom list calculations, editing the actual documents stored in your lists shouldn't be complicated. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office for editing your essential files.

1. Download WPS Office: Visit the official WPS website and download the free version for your operating system.
2. Install and Launch: Follow the installation prompts and open the application to access Writer, Spreadsheet, and Presentation.
3. Open Your Documents: Open your exported SharePoint documents directly in WPS Office with perfect formatting retention.

Fully compatible with Microsoft Office formats (.docx, .xlsx, .pptx) downloaded from SharePoint.Lightweight installation ensures fast startup and smooth document editing.Familiar, intuitive user interface that requires no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SharePoint calculated column show NaN in JSON formatting?

Calculated columns that output dates often do not pass the underlying date object correctly to JSON formatting scripts. When mathematical operations are applied to these unrecognized formats, it results in a 'Not a Number' (NaN) error.

How do I calculate days since created in SharePoint JSON?

You can calculate the days since an item was created by subtracting the system '[$Created]' field from the '@now' token, and dividing the result by the number of milliseconds in a day (86400000).

Can I format date columns in SharePoint without writing JSON code?

Yes, SharePoint offers out-of-the-box column formatting options. By clicking 'Column settings' and 'Format this column', you can apply basic conditional formatting and date highlighting using the visual interface without entering Advanced Mode.

Document & File Issues