How to Calculate Current Age from Date of Birth in Excel
There are many Excel functions you can use to help you calculate a person's age. The best feature to use depends on the format you want to use to display your age.
How to Calculate Current Age for a Whole Year
If you want to calculate someone's current age based on their date of birth, your best option is to use the YEARFRAC function. This will return the total number of completed years plus the current year's score.
Since people usually give their age in complete years, we can truncate the result to remove the decimal part and leave only the number of years.
Calculate age using YEARFRAC:
- Open Excel.
- Click the cell where you want to display age.
- Type=TRUNC(YEARFRAC(
- Select the cells containing the date of birth.
- Type ,TODAY())) and press Enter.
- Calculate a person’s age.
- If you have other ages you need to calculate, click the cell containing the formula, then click and hold the small square in the lower right corner of the cell.
- Drag down to apply the formula to other cells.
- Release the mouse - your other ages will be calculated.
How to Calculate Current Age in Years and Months
The above method can determine a person's age based on their date of birth, but you may want to be more precise The way. You can use different functions to calculate someone's age in years and months.
To calculate age in years and months in Excel:
- Click the cell where you want your age to appear.
- Enter=DATEDIF(
- Click the cell containing the date of birth.
- Enter ,TODAY(),”Y”) & “Years and” & DATEDIF(
- Click again on the cell containing the date of birth .
- Enter ,TODAY(),”YM”) & ” Months” and press Enter.
- Will calculate the age in years and months.
- To apply the formula to other cells, click and hold the square in the lower right corner of the cell and drag it down.
- Now calculate other ages.
How to calculate current age in years, months and days
If you want to be more accurate, you can include the year, month and day. This uses the same DATEDIF formula three times to calculate each section separately.
To calculate age in years, months and days in Excel:
- Click the cell where you want to display age.
- Enter=DATEDIF(
- Click the cell containing the date of birth.
- Enter ,TODAY(),”Y”) & “Years and” & DATEDIF(
- Click again on the cell containing the date of birth .
- Enter ,TODAY(),”YM”) & ” Months and”DATEDIF(
##Click again to include the birth The cell of the date. - Enter
- ,TODAY(),”MD”) & “Days” and press Enter.
Age will be calculated in year, month and day. -
To apply the formula to other cells, click and hold the small square in the lower right corner of the cell and drag it down. -
Other ages will be calculated. -
How to calculate age on a specific date in Excel
All the above methods use the
TODAY function to calculate a person’s date of birth and The difference between today's dates and gives their age today. You can calculate someone's age on any date of your choice by replacing TODAY with the date you want to use.
To calculate age on a specific date in Excel:
Click on the cell where you want the age to appear. - Type
- =TRUNC(YEARFRAC(
Click the cell - that contains the date of birth.
Type a comma and then click the cell that contains the specific date you want to use. -
Type two closing brackets and then press - Enter.
Age will be calculated for the specified date. -
If the specified date is changed, age will be automatically recalculated. -
Harness the power of Excel
Excel can be used for more than just data tables. There are tons of functions you can use to calculate almost anything you want Something to ask for. Knowing how to calculate age based on date of birth in Excel is just an example.
With the help of related functions in Excel you can calculate years of service in order to calculate bonus payments. You can use functions to calculate age from dates in Excel Delete times in stamps. You can even have Excel alert you when your data reaches certain values.
The above is the detailed content of How to calculate age from date of birth in Excel. For more information, please follow other related articles on the PHP Chinese website!