SharePoint Document Issues

How to Calculate Days Remaining Until a Date in SharePoint

Kushani Nimanthika Oct 10, 2026 869 views

Question details

The user needs to calculate the remaining days until a specific date in SharePoint but encounters a 'NaN' error in the column-formatting formula after refreshing.

How to Calculate Days Remaining Until a Date in SharePoint

Product: Microsoft SharePoint
Device & OS: Windows 10
Scenario: Creating a custom column in a SharePoint list to track task deadlines and days remaining using a calculated date.
Observed behavior: The column-formatting formula initially works but displays a 'NaN' (Not a Number) error after a page refresh.

Before you start

Ensure you have list-editing permissions in your SharePoint site and verify that your local computer's date and time settings are synced properly.

Solution 1Recommended

Update JSON Column Formatting to Schema Version 2

Resolves the 'NaN' error by explicitly calculating the millisecond difference between the target date and the current date using the updated schema.

SharePoint sometimes fails to parse date differences correctly using the older schema. Forcing it to evaluate the date as a numeric timestamp (milliseconds) prevents the NaN output.

Access Column Formatting

Navigate to your SharePoint list, click the column header where you want to display the remaining days, select 'Column settings', and click 'Format this column'.

Switch to Advanced Mode

At the bottom of the formatting pane, click 'Advanced mode' to view the JSON editor.

Update the Schema

Ensure your JSON includes the updated schema: "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json".

Apply Millisecond Formula

Set your text content formula to calculate the difference in milliseconds and divide by 86,400,000 (the number of milliseconds in a day): "txtContent": "=floor((Number([$TargetDate]) - Number(@now)) / 86400000)".

Save and Refresh

Click 'Save' at the bottom of the pane, then refresh the SharePoint page to verify the NaN error is resolved.

Update JSON Column Formatting to Schema Version 2

Syntax check: Make sure to replace [$TargetDate] with the actual internal name of your date column.

Solution 2

Create a Dedicated Calculated Column

Bypasses JSON formatting issues entirely by using SharePoint's native calculated column formulas instead of frontend formatting.

Solution 3

Verify Regional Settings and Test in Edge

Fixes discrepancies where the date calculation works on some computers but fails on others due to time zone or browser caching issues.

Free Microsoft Office alternative

Track Dates and Deadlines Easily with WPS Office

While SharePoint lists are great for team collaboration, managing complex date calculations and tracking deadlines is often much easier in a dedicated spreadsheet. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office, featuring built-in date functions to track remaining days effortlessly without encountering complex JSON formatting errors.

1. Download and Install: Visit the official WPS Office website to download and install the free software suite.
2. Open your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel deadline tracker or create a new workbook.
3. Apply Date Formulas: Use standard spreadsheet formulas like =A2-TODAY() to instantly calculate remaining days.

Free, lightweight, and fast-loading spreadsheet application.Fully compatible with Microsoft Excel formats (.xlsx, .xls) for seamless file sharing.Built-in date and time functions for precise deadline tracking without JSON scripting.Familiar user interface ensures a seamless migration with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SharePoint column formatting show 'NaN' for dates?

The 'NaN' (Not a Number) error typically occurs when the column-formatting JSON attempts to perform mathematical operations directly on a date string rather than a numeric timestamp. Updating your JSON schema to version 2 and converting the dates to milliseconds before dividing by 86,400,000 usually resolves this.

Can I use Excel-style formulas in SharePoint calculated columns?

Yes, SharePoint calculated columns support many standard Excel formulas. For tracking days, you can use the DATEDIF function or simply subtract TODAY() from your target date column, just as you would in a traditional spreadsheet.

How do I format the SharePoint column to highlight overdue tasks?

You can use the built-in conditional formatting rules. In the column settings, select 'Format this column', choose 'Conditional formatting', and set a rule to change the background color to red if the 'Days Remaining' column is less than zero.

Document & File Issues