How to Calculate a Birth Date from Age and Death Date in Excel
Question details
The user needs to estimate a birth date using a person's age and their date of death, and then safely delete the source columns without triggering a reference error.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing database records for import where source data columns must be deleted to save space after the estimated birth date is calculated.
- Observed behavior
- Using a standard calculation formula links the result to the source columns, causing a #REF! error in the result column if the age or death date columns are deleted.
Ensure your death date column is properly formatted as Dates in Excel, and that your age column contains numerical values representing full years.
Use the EDATE Function and Paste as Values
Calculate the birth date by subtracting the age in months from the death date, then convert the formula to static values so source columns can be safely deleted.
The EDATE function calculates a date a specific number of months before or after a starting date. By converting the age into months and subtracting it from the death date, you can estimate the birth date.
To prevent Excel from breaking the results when you delete the original data columns, you must convert the formula outputs into plain, static values.
Select the cell where you want the birth date to appear (e.g., D2). Enter the formula =EDATE(E2,-C2*12), assuming E2 contains the death date and C2 contains the age. Press Enter.
If some death dates might be blank and you want to calculate the birth date based on today's date instead, use the formula =EDATE(MIN(E2,TODAY()),-C2*12).
Click and drag the fill handle to apply the formula to your entire column. Highlight all the calculated birth dates, then press Ctrl+C (or right-click and select Copy).
Right-click the highlighted cells. Under 'Paste Options', click the 'Values' icon (usually displaying '123'). Alternatively, click 'Paste Special', choose 'Values', and click OK.
Now that the birth dates are static text rather than formulas, you can safely select columns C or E, right-click the column header, and select 'Delete' without causing a #REF! error.
Calculate Dates and Manage Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date functions like EDATE and Paste Special features, making it incredibly easy to prepare, calculate, and clean up data for database imports.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your file containing the age and death date records.
- 2. Apply the calculation formula: Type =EDATE(E2,-C2*12) in your target column to calculate the estimated birth date.
- 3. Convert to Values: Copy the formula results, right-click, and select 'Paste as Values' to remove the formulas.
- 4. Delete columns and save: Delete the original age and death date columns to free up space, then save your file for import.
Frequently Asked Questions
Why do I get a #REF! error when I delete the age column?
The #REF! error occurs because your calculated birth date formula still relies on the data in the deleted column. To fix this, you must copy the calculated results and use 'Paste Special' > 'Values' to convert the formulas into plain, static text before you delete any source columns.
How does the EDATE formula calculate the birth date?
The EDATE function adds or subtracts a specific number of months from a given date. In the formula =EDATE(E2, -C2*12), it multiplies the person's age in years (C2) by 12 to convert it to months, and then subtracts that total from the death date (E2) to estimate the birth date.
What happens if the recorded age is already in months instead of years?
If your age column already represents months, you do not need to multiply it by 12. You can simply use the formula =EDATE(E2, -C2) where C2 is the total age in months.
