How to Fix SharePoint Calculated Date Column Showing Time Incorrectly
Question details
The user needs to remove the time portion from a SharePoint calculated date column and prevent default 1900 dates from appearing when the source date is blank.

- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating or editing a SharePoint calculated column that adds a specific number of days to a start date.
- Observed behavior
- The calculated column continues to display both date and time despite being configured as Date Only, and shows a default 1900 date when the start date field is left blank.
Ensure you have the necessary 'Design' or 'Full Control' permissions to modify list or library settings in your SharePoint site.
Use TEXT and IF Formulas to Format the Calculated Column
The most reliable way to force a Date Only display and properly handle empty fields in SharePoint is by using explicit formatting formulas.
By wrapping your calculation in a TEXT function, you can explicitly define how the date should render. Adding an IF statement ensures that blank fields do not default to December 30, 1899, or January 1, 1900.
Navigate to your SharePoint List or Library. Click on the 'Settings' gear icon in the top right corner, then select 'List settings' or 'Library settings'.
Scroll down to the 'Columns' section and click on the name of your calculated column to open its editing page.
To explicitly format the result as Date Only, update your formula to: =TEXT([Start Date]+[Days],"d/m/yyyy"). Make sure to replace column names with your exact internal column names.
To prevent a default 1900 date when the start date is empty, modify the formula to include an IF condition: =IF([Start Date]="","",TEXT([Start Date]+[Days],"d/m/yyyy")).
Set 'The data type returned from this formula is' to 'Single line of text' (since the TEXT formula outputs text), then click 'OK' to save.

Verify the Source Date Column Settings
Sometimes a calculated column inherits the time format if the referenced source column still includes time data.
Manage Exported SharePoint Data in WPS Office
While SharePoint lists are managed online, users frequently export list data to Excel for deeper analysis. WPS Office is a highly compatible, lightweight, and free alternative to Microsoft Office that perfectly handles exported SharePoint data, complex date formulas, and formatting.
- 1. Export Data from SharePoint: In your SharePoint list, click 'Export' from the top menu and select 'Export to Excel'.
- 2. Open with WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open your downloaded .iqy or .xlsx file.
- 3. Analyze and Format Dates: Use WPS Spreadsheet's built-in date formatting tools and formulas to seamlessly process your data offline.

Frequently Asked Questions
Why does my SharePoint calculated column show a date from 1899 or 1900?
SharePoint calculates dates identically to Excel, storing them as serial numbers starting from late 1899/early 1900. When a source date field is blank, the calculation treats its value as zero, displaying the zero-value serial date. Using an IF statement to check for blank fields (="") prevents this issue.
Will formatting the date as text affect how the column sorts?
Yes. When you use the TEXT formula, the returned data type is a text string. SharePoint will sort this column alphabetically rather than chronologically. If you need chronological sorting, it is better to use the format "yyyy-mm-dd" so alphabetical sorting matches chronological order.
Can I use the exact same TEXT and IF formulas in Excel?
Yes, SharePoint calculation formulas are heavily based on Excel functions. The exact formula =IF([Start Date]="","",TEXT([Start Date]+[Days],"d/m/yyyy")) works similarly in Excel, though you would reference cell coordinates (like A2 and B2) instead of SharePoint column names.




