How to Calculate a Date of Birth from Age in Excel
Question details
The user wants to know if and how Excel can calculate a person's exact or estimated Date of Birth using only their age.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Estimating a birth year and date based on a given age and a reference date (like today's date).
- Observed behavior
- An exact date of birth cannot be found from age alone, but an estimated birth date or birth year can be calculated using Excel formulas.
Keep in mind that calculating a date of birth from age alone will only provide an estimated birth date. An exact birthday requires knowing the specific month and day.
Use the EDATE Function to Estimate Date of Birth
This is the most efficient method to calculate an estimated birth date by subtracting years (converted to months) from a reference date.
The EDATE function allows you to add or subtract months from a specified date. By multiplying the age by 12, you can subtract the total number of months from today's date or a specific reference date to find the estimated birthday.
Enter the person's age in complete years into a cell, for example, cell A2.
Click on the empty cell where you want the calculated date of birth to be displayed.
Type the formula =EDATE(TODAY(),-12*A2) if you are assuming today is the reference date, and press Enter.
If you have a specific reference date in cell B2, use the formula =EDATE(B2,-12*A2) instead.
If the result shows as a plain number, right-click the cell, select 'Format Cells', choose 'Date', and click OK.
Calculate Birth Date using YEAR, MONTH, and DAY Functions
Extract the year, month, and day from a reference date, subtract the age from the year, and combine them back together.
Calculate Dates Easily with WPS Spreadsheet
You can effortlessly perform date calculations, including finding birth dates from ages, using WPS Spreadsheet. It fully supports all standard Excel functions like EDATE, YEAR, and TODAY.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing the age data.
- 2. Select the target cell: Click the cell where you want to output the estimated Date of Birth.
- 3. Enter the formula: Type =EDATE(TODAY(),-12*A2) (assuming the age is in cell A2) and press Enter.
- 4. Format the output: Right-click the cell, click 'Format Cells', and select your preferred Date format.
Frequently Asked Questions
Why does my EDATE formula return a random 5-digit number?
Spreadsheet programs store dates as sequential serial numbers for calculation purposes. If your formula returns a 5-digit number (like 32541), simply right-click the cell, select 'Format Cells', and change the number format to 'Date'.
Can I calculate the exact date of birth if I only have the age?
No, an exact date of birth requires knowing the exact month and day. Age alone only allows you to calculate the birth year or generate an estimated birth date relative to an assumed reference date.
How do I calculate just the birth year from age in Excel?
To get only the birth year without a specific day and month, select a blank cell and use the formula =YEAR(TODAY())-A2, replacing A2 with the cell reference that contains the person's age.
