How do I reflect a date before 1900 in Excel?

Question

I am attempting to compile a spreadsheet of data before 1900. I cannot seem to reflect the date properly without formatting it as text. I want to be able to add 1 to my base date and ultimately value and sort dates.

Answer 1

Let me know how it would go. Thanks.

Answer 2

Hello, what version of Excel are you using?

Use Power Query to handle dates before 1900. Go to Data, Get Data, then load your table into Power Query. Change the column type to Date inside Power Query, which supports years earlier than 1900. Do your sorting and transformations there, then load the result back to Excel. This lets you work with real date values and sort or increment them correctly within the query engine.

Answer 3

Excel’s built‑in date system does not support real date values earlier than 1/1/1900. Any “date” before 1900 must be stored as text or handled with a custom workaround; Excel cannot natively treat pre‑1900 dates as serial dates that can be incremented, sorted, and used in date functions.

Key limitations from Excel’s date engine:

• Valid date range is 1/1/1900 through 12/31/9999.
• Dates before 1900‑01‑01 are not supported as true dates and will not behave correctly if forced into date format.

Because of this, for dates before 1900 there is no built‑in way to:

• Add 1 day by simple date arithmetic (e.g., =A1+1) on a true Excel date value, or
• Reliably sort them as dates using Excel’s native date type.

To work with pre‑1900 dates, use one of these approaches:

1. Store the dates as text (e.g., "12/31/1899") and:
   • Add a helper column that converts them to a numeric scale (for example, a custom serial number or year/month/day split) for sorting and calculations.
   • Use formulas based on that numeric representation instead of Excel’s date serial.
2. Store a parallel “offset” date in a supported range (for example, map 1800–1899 onto some 2000–2099 range) and:
   • Do all arithmetic and sorting on the offset date.
   • Display the historical date as text in another column.

However, even with workarounds, Excel itself will not treat pre‑1900 values as native dates.

---

References:

• Differences between the OpenDocument Spreadsheet (.ods) format and the Excel for Windows (.xlsx) format
• How Excel works with two-digit year numbers
• Excel incorrectly assumes that the year 1900 is a leap year