'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Why Excel Cannot Format Dates Before 1900 & How to Fix It
Question details
The user is attempting to input and format historical dates (e.g., 3 June 1877) in Excel but finds that the software cannot apply standard long or short date formatting to these entries.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering and managing historical dates for genealogy, historical research, or long-term data tracking.
- Observed behavior
- Excel fails to recognize dates prior to January 1, 1900, treating them as plain text rather than functional date values, which prevents standard date formatting and time intelligence calculations.
Determine whether you need to perform mathematical calculations on these historical dates or if you simply need to display them accurately in your spreadsheet. Your goal will dictate the best workaround to use.
Use Power Query for Pre-1900 Dates
Power Query uses a different data type system that supports dates spanning a much wider historical range, making it the ideal solution if you need to process or transform older dates.
Excel relies on a serial number system starting from January 1, 1900, and it cannot process negative serial numbers. Power Query, however, does not have this limitation and can recognize and format dates well before the 19th century.
Select the range of cells containing your historical date text, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, locate your date column, click the data type icon in the column header (usually displaying 'ABC' or 'Any'), and select 'Date' from the dropdown menu.
Once the dates are correctly recognized and formatted in the editor, click 'Close & Load' on the Home tab to output the processed data into a new worksheet.
Format Pre-1900 Dates as Text
If you only need to display historical dates visually without performing duration or age calculations, formatting the cells as text is the quickest method.
Try WPS Office for Seamless Spreadsheet Management
The 1900 date system limitation is an industry standard across most traditional spreadsheet software, including Excel. If you're looking for a highly efficient, cost-effective alternative to manage your data, WPS Office offers a powerful spreadsheet tool that is fully compatible with all your existing files. It provides flexible text formatting, advanced filtering, and a familiar interface without the hefty subscription fees.
Frequently Asked Questions
Why does the Excel date system begin in 1900?
Excel calculates dates using a serial number system where January 1, 1900, is designated as serial number 1. Because the system was not programmed to recognize negative serial numbers, any date prior to 1900 cannot be calculated natively.
Can I use the 1904 date system to fix this issue?
No. Switching to the 1904 date system (found in Excel Options > Advanced) merely shifts the starting serial number to January 1, 1904. It does not enable the software to format or calculate dates from the 1800s or earlier.
How can I calculate age for birthdates before 1900?
Since native date formulas won't work, you must split the year, month, and day into separate columns using text formulas (like LEFT, MID, and RIGHT), and then perform manual mathematical subtractions on the year column to determine age.
Does this 1900 limitation apply to other spreadsheet programs?
Yes. Most major spreadsheet applications adopted the 1900 date system to maintain historical cross-compatibility with early programs like Lotus 1-2-3, meaning you will encounter similar limitations in software like Google Sheets and WPS Spreadsheet.
