How to Format Dates Before and After 1900 Correctly in Excel
Question details
The user needs to display historical dates (before 1900) and modern dates consistently as day, abbreviated month, and four-digit year without using apostrophe prefixes.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering historical and modern dates into a spreadsheet for unified display and sorting.
- Observed behavior
- Dates after 1900 automatically convert to short date formats, while pre-1900 dates remain recognized as simple text strings, causing inconsistent visual formatting and preventing chronological sorting.
Keep in mind that Excel's built-in date system starts on January 1, 1900. Any date before this is automatically treated as plain text, meaning standard date formatting options and chronological sorting will not apply directly.
Store Date Components in Separate Columns
The most reliable method for handling historical dates for calculations and sorting is to split the day, month, and year into separate columns.
Because Excel cannot process pre-1900 dates as serial numbers, storing the entire date in a single cell limits your ability to sort or filter chronologically. Separating them allows you to sort by year, then month, then day.
Insert three new columns adjacent to your data and label them 'Year', 'Month', and 'Day'.
Enter the four-digit year in the Year column, the abbreviated month text (or number) in the Month column, and the day in the Day column.
Highlight your data range, navigate to the Data tab, and select Sort. Add sorting levels to sort by Year first, then Month, then Day.
Standardize Display Using Text Formulas
If you only need a uniform visual display across all entries (both pre-1900 and post-1900), you can convert all dates to text using a specific format.
Manage Your Spreadsheets Seamlessly with WPS Office
The 1900 date limitation is an industry-standard spreadsheet behavior originally established by Microsoft Office. For a lightweight, powerful, and highly compatible alternative, try WPS Office. It provides robust text handling, advanced sorting, and all the formulas you need to manage historical data seamlessly.
- 1. Download WPS Office: Visit the official WPS website to download and install WPS Office for free.
- 2. Open your workbook: Launch WPS Spreadsheets and open your existing file with historical dates.
- 3. Apply text formulas: Use the built-in TEXT functions and column splitting features seamlessly, just as you would in Microsoft Excel.
Frequently Asked Questions
Why does Excel not recognize dates before 1900?
Excel relies on the 1900 date system, meaning it tracks dates as sequential serial numbers starting from January 1, 1900. Dates prior to this do not have a serial number in Excel's underlying architecture, so the software defaults to treating them as plain text.
Can I calculate the number of days between two dates before 1900?
Not directly using standard mathematical subtraction. You must extract the year, month, and day into separate columns to calculate the difference, or use a custom VBA macro designed to calculate historical date differences.
Is there a way to force Excel to accept pre-1900 dates as real dates?
There is no built-in native setting to change the base date system backward. However, you can utilize third-party Excel add-ins specifically designed for historical and genealogical data to handle pre-1900 date calculations.
