Birthdays & Ages: Handling “Date of Birth” errors for employees born before 1900.

Excel cannot natively calculate dates of birth before January 1, 1900, because its default calendar system treats pre-1900 dates as unformatted text strings rather than numeric serial numbers. If you attempt to calculate an age, retirement vesting date, or pension survivor benefit using standard functions like DATEDIF, YEARFRAC, or simple subtraction on a pre-1900 date, Excel immediately returns a #VALUE! or #NUM! error.

Quick Answer

To calculate current age from a pre-1900 birthdate stored as text (e.g., "10/14/1898" in cell A2), parse the year, month, and day manually using this formula:

=YEAR(TODAY()) - RIGHT(A2, 4) - IF(DATE(YEAR(TODAY()), MID(A2, 1, 2), MID(A2, 4, 2)) > TODAY(), 1, 0)

Adjust the MID and RIGHT character positions if your dates use a format other than MM/DD/YYYY.

---

Why Excel Breaks on Pre-1900 Dates

Excel stores all dates as continuous sequential serial numbers. Under the standard Windows 1900 date system:

• Day 1 is January 1, 1900.
• Day 2 is January 2, 1900.
• Today’s date is simply an integer representing the number of days elapsed since that baseline.

Because negative serial numbers are not supported in Excel’s date engine, any date prior to 01/01/1900 cannot be converted to a serial value. Excel leaves it as a text string.

In HR and payroll environments, this issue typically appears in:

1. Pension and Annuity Administration: Managing legacy pension rosters, lifetime annuity funds, or non-employee surviving beneficiaries.
2. Historical Rosters & Genealogy Records: Archiving long-tenured company registry data.
3. Data Entry Typos: An employee born in 1998 being mistyped as 1898, or a two-digit year entry like 98 being misassigned by system import logic.

When a date is treated as text, functions like YEAR(), MONTH(), and DAY() fail with #VALUE!. For more context on text-date errors, see #VALUE! in Subtraction: Dates stored as Text.

---

Diagnostic Step: Confirming the Date Data Type

Before applying a complex formula, check whether Excel sees the cell as a number or text. In an empty column, enter:

=ISNUMBER(A2)

• TRUE: The date is recognized by Excel (it falls between 1900 and 9999). Standard formulas like DATEDIF will work.
• FALSE: The date is text. Excel cannot perform serial date arithmetic on it directly.

---

Solutions for Calculating Age with Pre-1900 Birthdays

Depending on your data source and spreadsheet structure, use one of the following methods to calculate accurate ages.

Method 1: Mathematical String Parsing (Standard HR Fix)

If your date is formatted consistently as MM/DD/YYYY (for example, 08/25/1895):

=YEAR(TODAY()) - VALUE(RIGHT(A2, 4)) - IF(DATE(YEAR(TODAY()), VALUE(LEFT(A2, 2)), VALUE(MID(A2, 4, 2))) > TODAY(), 1, 0)

How this formula works:

1. YEAR(TODAY()) - VALUE(RIGHT(A2, 4)) finds the difference between the current year and the birth year.
2. DATE(YEAR(TODAY()), VALUE(LEFT(A2, 2)), VALUE(MID(A2, 4, 2))) reconstructs the employee’s birthday in the current year.
3. IF(... > TODAY(), 1, 0) checks whether the birthday has occurred yet this year. If the birthday is still in the future, it subtracts 1 from the age.

Method 2: The Century-Shift Workaround (For Full Date Functions)

If you need to use YEARFRAC or DATEDIF for actuarial precision, shift the birthdate forward into the supported range by adding 400 years (since leap year cycles repeat cleanly every 400 years in the Gregorian calendar):

1. Extract and shift the date forward: =DATE(VALUE(RIGHT(A2, 4)) + 400, VALUE(LEFT(A2, 2)), VALUE(MID(A2, 4, 2)))
2. Shift the comparison date forward by 400 years: =DATE(YEAR(TODAY()) + 400, MONTH(TODAY()), DAY(TODAY()))
3. Run DATEDIF across the shifted dates: =DATEDIF(ShiftedBirthDate, ShiftedCurrentDate, "Y")

Because both dates were shifted by the exact same 400-year Gregorian cycle, the elapsed leap days and year counts match real-world calendar rules without triggering #VALUE! or #NUM!. For guidance on how leap days influence tenure calculations, review Tenure Calculation: Handling leap years in employee “Years of Service” formulas..

---

Watch Out for the Two-Digit Year Import Bug

If you import payroll data from CSV files containing two-digit years (such as 05/12/24), Windows and Excel apply an automatic century cutoff rule (typically 1930 to 2029):

• Any two-digit year from 00 to 29 defaults to 2000–2029.
• Any two-digit year from 30 to 99 defaults to 1930–1999.

If an employee was born in 1924 and entered as 24, Excel will parse the year as 2024. When you attempt to calculate their age, DATEDIF(BirthDate, TODAY(), "Y") returns 0 or throws #NUM! because the birthdate is evaluated as being in the future. To fix this condition, see #NUM! in DATEDIF: When Start Date is after End Date.