Others

Fix SharePoint Calculated Column Showing 12/30/1899 Instead of Blank

Muhammad Talha Oct 9, 2026 869 views

Question details

The user needs to prevent a calculated column from displaying '12/30/1899' when the evaluated date field is empty.

How to Fix SharePoint Calculated Column Displaying 12/30/1899

Product: SharePoint
Device & OS: not provided
Scenario: Creating or modifying a calculated column formula in a list to conditionally output a date value.
Observed behavior: The calculated column outputs the default zero-date '12/30/1899' instead of remaining blank when checking conditional fields.

Before you start

Ensure you have list management permissions to access and modify the column settings in your SharePoint site.

Solution 1Recommended

Update the Calculated Column Formula Dependency

Modify the formula to explicitly evaluate the target date field so that it correctly returns a blank value instead of a zero-date.

This issue typically occurs when a calculated column returns an empty or zero value, which the system interprets as the baseline date of December 30, 1899. By adjusting the formula to check if the specific date field is populated (rather than checking a secondary action field), you can force the column to display a true blank.

Access List Settings

Navigate to your SharePoint list, click the gear icon in the top right corner, and select 'List settings'.

Edit the Calculated Column

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

Revise the Formula

In the Formula box, update the logic to check the actual date field. Use an IF and ISBLANK function combination, such as: =IF(ISBLANK([Action Date]),"",[Action Date]). This ensures the calculation depends directly on the date field being populated.

Save and Verify

Click 'OK' to save the changes. Return to your list view to verify that the empty date fields now display as blank instead of 12/30/1899.

Update the Calculated Column Formula Dependency

Test in a New List: If the issue persists, create a new temporary list and test your revised formula there. This helps isolate the problem from any existing column corruption.

Free Microsoft Office alternative

Manage Your Data Smoothly with WPS Office

While web-based lists are great for basic tracking, managing complex datasets and date formulas is often more reliable in a dedicated spreadsheet. WPS Spreadsheet offers a powerful, free, and lightweight alternative for flawless data calculation.

1. Export Your List: Export your current web-based list data to a CSV or Excel file format.
2. Open in WPS Spreadsheet: Launch WPS Office and open your exported data file to access advanced formatting tools.
3. Apply Formulas Flawlessly: Use intuitive, Excel-compatible formulas to manage your date columns efficiently without formatting errors.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Advanced date and time calculation functions without zero-date bugs.Lightweight installation with a familiar, easy-to-use tabbed interface.Free built-in PDF creation and seamless data sharing features.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the system show 12/30/1899 for blank dates?

In many Microsoft databases and spreadsheet systems, date values are stored as sequential serial numbers starting from a specific epoch. When a formula returns an empty or zero value to a Date/Time column, the system interprets '0' as its baseline date, which defaults to December 30, 1899.

Can I use the ISBLANK function to fix this error?

Yes, wrapping your date field reference in an IF and ISBLANK function ensures the column returns an empty text string instead of a zero-date. The syntax generally looks like =IF(ISBLANK([MyDate]),"",[MyDate]).

Does this 1899 date issue affect exported lists?

Yes. If the calculated column displays 12/30/1899 in the web view, exporting the list to Excel or CSV will carry over that incorrect date value instead of leaving the spreadsheet cell blank. Fixing the formula beforehand prevents dirty data during exports.

Office Quick Solutions