How to Record Dates Before 1900 in Excel (The Bug That Breaks Family Tree Spreadsheets)

There is a specific moment when a family tree spreadsheet stops working, and almost everyone who has built one has hit it without knowing what happened.

The file is fine for three generations. Ages calculate. The timeline sorts. Then you get past your grandparents, start entering great-great-grandparents born in the 1860s, and things go subtly wrong. An age column returns #VALUE! on some rows and a number on others. The timeline sorts 1868 after 2019. A lifespan comes back as 40,000.

Nothing is corrupted. You have hit the edge of Excel’s calendar.

What Is Actually Happening

Excel does not store dates as dates. It stores them as serial numbers counted forward from an epoch at the start of January 1900. Every date you have ever typed is really an integer — a count of days — dressed up by cell formatting.

Which means there is simply no serial number available for a date before that epoch.

Type 19 Jan 1868 into a date-formatted cell and Excel cannot store a date, so it does the only other thing available: it keeps your keystrokes as text. The cell looks correct. It is left-aligned instead of right-aligned, which is the only visible clue, and almost nobody notices.

From that point, three things are quietly broken:

  1. Sorting goes alphabetical. Text sorts as text, so 19 Jan 1868 files near 19 Jan 2019, and a timeline built on that column is meaningless.
  2. Arithmetic fails or lies. =YEAR(A2) on text returns an error. Worse, a mixed column — some real dates, some text — produces formulas that work on some rows, which reads as a data-entry problem rather than a structural one.
  3. The damage is invisible until it is far away. The formula that breaks is often three tabs from the cell that caused it.

This is the single most common reason free family tree templates work beautifully in demo and then fail on a real tree. The demo family was born after 1900.

The Fix: Store Every Historical Date Twice

The solution is not a workaround. It is a better data model, and it is what dedicated genealogy software does internally anyway.

Every historical date gets two columns.

Birth Date (as written) — text Birth Year — number
abt 1870 1870
19 Jan 1868 1868
bef 1901 1900
1836 1836
Q2 1889 1889
(unknown) (blank)

Two rules make it work:

Rule 1: all arithmetic reads the year column. Ages, lifespans, generation spans, census age comparisons, timeline ordering. Never the text column. The year column is a plain number, and a plain number behaves identically in 1780 and 2026.

Rule 2: the as-written column is never tidied up. More on why below.

Apply this to every historical date in the file: birth, death, marriage, immigration, burial. It is a little more typing per person and it is the difference between a file that lasts and one that gets rebuilt.

Modern Dates Stay Normal

Do not over-apply this.

Dates that describe your research rather than your ancestors — date accessed, target date for a task, planned interview date, subscription renewal — are all after 1900 by definition. Leave them as ordinary date values. They sort, filter and count down normally, and a days-until-renewal formula needs a real date to work.

The two-column rule is for historical dates only.

Why You Never Clean Up “abt 1870”

This is the part people fight, and it matters more than the technical fix.

abt, bef, aft and circa are not sloppy data entry. They are the record telling you how confident it was, and that confidence is evidence in its own right.

Consider two ancestors, both showing a Birth Year of 1870:

In the year column they are identical. In reality one is a fact and the other is an estimate with a plausible error of several years. If you delete the as-written column — or “clean” both to 1870 — you have destroyed the only thing that distinguishes them, and in eighteen months you will treat the estimate as a record.

Keep the text. It costs a column.

bef 1901 deserves a note of its own: the working year you derive is a boundary, not an estimate. Deriving 1900 is a convention that lets the arithmetic run; it is not a claim that they were born in 1900. If your file has many of these, a small “date qualifier” column (exact / about / before / after) makes the distinction machine-readable and lets you filter on it.

Calculating Age Without Real Dates

The obvious objection: without real dates, can you still calculate anything useful?

Yes — and at the precision the records actually support.

Age at death = death year − birth year. Accurate to within a year. A parish register that says abt 1870 cannot support day-level arithmetic no matter how the cell is formatted, so day-level precision would be false precision.

Age at any event = event year − birth year. Which is exactly how you catch an event dated before someone was born or after they died — one of the most useful checks in a genealogy file, and one that only works if both sides are plain numbers.

Generation span = child’s birth year − parent’s birth year. A span under 14 or over 55 is worth a second look; not impossible, but frequently a sign that a generation has been skipped or two people with the same name have been merged.

Census age comparison — the one that pays for the whole structure. A census records an age; that age implies a birth year. Compare the implied year against the year you hold, and flag the gap.

The Census Check, Worked

Census ages were guessed, rounded, and sometimes plainly misreported. The same ancestor routinely carries three or four different implied birth years across four census returns.

Here is the calculation, on a hypothetical ancestor whose recorded birth year is 1868:

Census year Age recorded Implied birth year Variance
1871 3 1868 0
1881 13 1868 0
1891 22 1869 1
1901 29 1872 4

(Assumptions: a fictional individual, illustrating the arithmetic. Implied birth year = census year − recorded age, ignoring the month of enumeration, which is why a variance of one is normal noise.)

The 1891 variance of one year is nothing — it is the difference between someone’s birthday falling before or after enumeration day. The 1901 variance of four is a real signal. It means one of three things: the informant that year did not know his age, the enumerator misheard, or the man on that 1901 return is a different man.

Set the tolerance yourself — two years is a reasonable starting point — so that only the gaps worth chasing get raised and the ordinary one-year noise stays quiet.

None of this arithmetic is possible if the birth “date” is text.

One further discipline on census entries: record the name, age and birthplace exactly as the enumerator wrote them, misspellings included. Those misspellings are how you find the next record, because the index you search later was transcribed from the same handwriting.

Google Sheets, LibreOffice and Numbers

Google Sheets handles historical dates somewhat differently from Excel, and the behaviour is not identical across programs.

Rather than learning each program’s edge cases, build the file so it never depends on any of them. If every calculation reads a plain number, the workbook behaves the same in Excel, Google Sheets, LibreOffice Calc and Apple Numbers — which matters, because a genealogy file gets shared with relatives who use whatever is on their machine.

The Five-Minute Audit

If you already have a family tree spreadsheet, check it now:

  1. Select your birth date column. Real dates align right by default; text aligns left. A column with both alignments is a broken column.
  2. Try =COUNT(range) on it. COUNT only counts numbers. If your column has 200 entries and COUNT returns 140, sixty of them are text — and those sixty are almost certainly your earliest, most interesting ancestors.
  3. Sort by it and look at the oldest entries. If anything from the 1800s appears among the 2000s, that column is text.
  4. If it is broken, do not reformat. Reformatting a text cell to “Date” does not convert it. Add the year column, fill it, and re-point the formulas. It is an hour of work and it is permanent.

The full column layout this fits into — the individuals table, the ID system and the ahnentafel numbering that builds the chart from it: how to build a family tree spreadsheet that still calculates at five generations.

Featured on ReadySheetGo

Family Tree, Genealogy Research & Ancestry Record Organizer — $13.99

Built the other way round from the start. Every historical date has two columns — the date written exactly as the record gives it (abt 1870, 19 Jan 1868, bef 1901) and the year on its own in a number column — and all the arithmetic reads the year column, so ages, lifespans, census conflicts and timelines work for the 1700s exactly as they work for last year. The loaded sample tree reaches 1836 and every figure in it calculates. Modern working dates — date accessed, target date, planned interview, renewal — are ordinary dates and sort and filter normally.

The Census Grid tab is the check above, already built: one row per person per census, recorded exactly as the enumerator wrote it, misspellings and all, deriving the birth year the recorded age implies, comparing it against the year you hold and flagging the gap at a tolerance you set.

Plus Individuals with a completeness score and six row checks including the circular parent link; a five-generation Pedigree with 31 ahnentafel-numbered slots that redraws when you change the root person; Sources rating records Proven, Probable, Possible or Disproven; a Research Log that records the dead ends; DNA Matches with a Next Action column; Timeline with age-at-event checks; Interviews ranked by age; Heirlooms; Research Costs; and a Dashboard running 24 checks against your own entries.

16 linked tabs, 15,700 working formulas, a fictional sample family already loaded — 30 people, 26 sources, 12 DNA matches, 23 census rows.

Works in Excel, Google Sheets, LibreOffice Calc and Apple Numbers. No macros, no add-ons, no array formulas, no subscription. Works offline.

Get the Family Tree & Genealogy Research Organizer →

Frequently Asked Questions

Why can't Excel handle dates before 1900?

Excel stores dates as serial numbers counted from an epoch at the start of January 1900, so there is no serial number available for an earlier date. Typing 19 Jan 1868 into a date-formatted cell does not produce a date value — it produces text that looks like one. The cell then sorts alphabetically rather than chronologically, and any formula expecting a number returns an error, a zero, or a wrong answer that looks plausible, which is the dangerous outcome.

How do you calculate an ancestor's age if the date is not a real date?

Calculate from a plain four-digit year held in its own number column: death year minus birth year gives an age accurate to within a year, which is almost always the precision the underlying records support anyway. A parish register that says 'abt 1870' cannot support day-level arithmetic no matter how the cell is formatted. Storing the year as a number also makes sorting, filtering, generation spans and census comparisons work identically for 1780 and 2026.

Should you clean up dates like abt 1870 and bef 1901?

No. Those qualifiers are the record telling you how confident it was, and deleting them destroys evidence you will want back. Keep the date exactly as written in a text column and derive a working year into a separate number column. The as-written column is what reminds you, two years later, that 1870 was an estimate rather than a recorded fact — which changes how much weight the number should carry.

Does Google Sheets have the same 1900 limitation as Excel?

Google Sheets handles some earlier dates differently from Excel, but the behaviour is not identical between the two, and a genealogy file usually has to open in both. The safe approach is to build the file so it does not depend on either program's handling of historical dates at all — store the year as a plain number, point every formula at that, and the workbook behaves the same everywhere, including in LibreOffice and Apple Numbers.

You Might Also Like

Family Tree Spreadsheet Template for Excel: How to Build One That Still Works at Five Generations Family Tree Spreadsheet vs Ancestry Software: Which One Should You Actually Use? 401(k) vs Roth IRA vs HSA: Which to Fund First in 2026

25 of 31 Ancestor Slots Filled. 11 People With No Source Behind Them.

The Family Tree, Genealogy Research & Ancestry Record Organizer — 16 linked tabs and 15,700 working formulas, with a fictional sample family already loaded — 30 people, 26 sources, 12 DNA matches, 23 census rows and a tree reaching 1836 — so you can see the model running before you type anything. An **Individuals** tab is the single master list every other tab reads from: one row per person, entered once, carrying status, age, source count, a completeness score out of eight and a row check that catches a death dated before a birth, a parent ID that is not in the sheet, a lifespan over 110, a person listed as their own father, and the circular parent link that would otherwise fill a pedigree with the same two people forever. A **Pedigree** tab builds five generations and 31 ahnentafel-numbered ancestor slots entirely from the Father ID and Mother ID columns — change the root person and the whole chart redraws — reporting completeness per generation and showing empty slots in red, because an empty slot is not an error but a person who existed and whose name has not been found yet. A **Sources** tab rates every record Proven, Probable, Possible or Disproven, which is the mechanism that stops a family story quietly becoming a fact, and flags anything still being leaned on after you disproved it. A **Research Log** holds the open question, the record to search next and the target date — and records what you searched and found NOTHING in, so the same dead end is not paid for twice. A **DNA Matches** tab reads a relationship band from an editable lookup and then keeps asking the question every other sheet skips: ASSIGN ANCESTOR, CONTACT, Follow up or Confirmed, so a close match never sits unattached at the bottom of a list. A **Census Grid** records each entry exactly as the enumerator wrote it, misspellings included, derives the birth year the recorded age implies, compares it against the year you hold and flags the gap at a tolerance you set. Plus **Timeline** with age-at-event checks, **Interviews** ranking living relatives by age and raising anyone past your urgency threshold, **Heirlooms** flagging valued objects with no photograph, **Research Costs** with renewal warnings and an annual run rate, and a **Dashboard** running 24 data checks against your own entries. Every historical date is stored twice — as written ("abt 1870", "bef 1901") and as a plain year in a number column — so the arithmetic works in the 1700s exactly as it works for last year. Works in Excel and Google Sheets. No macros, no add-ons, no subscription, works offline. It is a research organiser, not genealogical proof: a clear check means your entries agree with each other, not that they are correct.

View on Etsy — $13.99