Why Excel Cannot Format Dates Before 1900 and How to Fix It
Question details
The user is trying to enter and format a historical date (like May 29, 1724) but the spreadsheet program does not recognize or format it as a valid date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering and formatting historical date data prior to the year 1900 into a spreadsheet cell.
- Observed behavior
- The software fails to apply standard date formats (like dd-mmm-yyyy) and treats the input as standard text because standard date systems only support dates starting from January 1, 1900.
Before proceeding, determine if you simply need to display the historical dates visually, or if you need to sort and filter them chronologically, as this will dictate the best workaround.
Format Pre-1900 Dates as Text
Convert the cell format to Text so you can type the historical date exactly as you want it to appear without the system trying to recalculate it.
Because Excel relies on a calendar system starting in the year 1900, it cannot mathematically process dates prior to that year. Formatting the cell as text prevents the software from attempting to validate the date.
Highlight the specific cells or column where you plan to enter dates prior to the year 1900.
Right-click on the selected area and choose 'Format Cells' from the dropdown menu.
Navigate to the 'Number' tab, select 'Text' from the Category list on the left side, and click 'OK'.
Type your date in your preferred format (e.g., 29-May-1724). The program will now store and display it exactly as entered.
Split Historical Dates into Separate Year, Month, and Day Columns
Store the date components in different columns to allow for easier chronological sorting and filtering of historical data.
Manage Historical Dates Easily with WPS Spreadsheet
WPS Spreadsheet offers intuitive text formatting and powerful data manipulation tools to help you manage pre-1900 dates effortlessly. Enjoy high compatibility with Microsoft Excel formats while organizing your historical records.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the historical records.
- 2. Apply Text formatting: Highlight the target cells, right-click to select 'Format Cells', choose 'Text' under the Number tab, and hit 'OK'.
- 3. Enter your historical dates: Type the pre-1900 dates. WPS Spreadsheet will retain your exact input cleanly without triggering automatic date calculation errors.
Frequently Asked Questions
Why does Excel's standard date system begin in 1900?
Excel's date system starts on January 1, 1900. This design choice was originally made to ensure strict backward compatibility with early spreadsheet software like Lotus 1-2-3. Because of this, any date before 1900 is not recognized as a serial number for mathematical date calculations.
Can I perform mathematical calculations on dates before 1900?
No, standard date calculations (such as adding days or finding the exact difference between two dates) will not work natively for pre-1900 dates because the software treats them as standard text strings rather than numeric values. Complex VBA macros are required to calculate them natively.
How do I correctly sort dates that are older than the year 1900?
Since pre-1900 dates must be stored as text, sorting them as a single string will result in alphabetical ordering, not chronological ordering. To sort them chronologically, separate the year, month, and day into distinct columns and use the Custom Sort tool to sort by the Year column first, then Month, then Day.
