Home  >  Article  >  How to calculate age from date of birth in Excel

How to calculate age from date of birth in Excel

王林
王林forward
2023-05-19 08:58:4730187browse

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:

  1. Open Excel.
  2. Click the cell where you want to display age.
  3. Type=TRUNC(YEARFRAC(
    How to calculate age from date of birth in Excel
  4. Select the cells containing the date of birth.
    How to calculate age from date of birth in Excel
  5. Type ,TODAY())) and press Enter.
    How to calculate age from date of birth in Excel
  6. Calculate a person’s age.
    How to calculate age from date of birth in Excel
  7. 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.
    How to calculate age from date of birth in Excel
  8. Drag down to apply the formula to other cells.
  9. Release the mouse - your other ages will be calculated.
    How to calculate age from date of birth in Excel

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:

  1. Click the cell where you want your age to appear.
  2. Enter=DATEDIF(
    How to calculate age from date of birth in Excel
  3. Click the cell containing the date of birth.
    How to calculate age from date of birth in Excel
  4. Enter ,TODAY(),”Y”) & “Years and” & DATEDIF(
    How to calculate age from date of birth in Excel
  5. Click again on the cell containing the date of birth .
    How to calculate age from date of birth in Excel
  6. Enter ,TODAY(),”YM”) & ” Months” and press Enter.
    How to calculate age from date of birth in Excel
  7. Will calculate the age in years and months.
    How to calculate age from date of birth in Excel
  8. To apply the formula to other cells, click and hold the square in the lower right corner of the cell and drag it down.
    How to calculate age from date of birth in Excel
  9. Now calculate other ages.
    How to calculate age from date of birth in Excel

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:

  1. Click the cell where you want to display age.
  2. Enter=DATEDIF(
    How to calculate age from date of birth in Excel
  3. Click the cell containing the date of birth.
    How to calculate age from date of birth in Excel
  4. Enter ,TODAY(),”Y”) & “Years and” & DATEDIF(
    How to calculate age from date of birth in Excel
  5. Click again on the cell containing the date of birth .
  6. Enter ,TODAY(),”YM”) & ” Months and”DATEDIF(
    How to calculate age from date of birth in Excel
  7. ##Click again to include the birth The cell of the date.
  8. Enter
  9. ,TODAY(),”MD”) & “Days” and press Enter.
    How to calculate age from date of birth in Excel
  10. Age will be calculated in year, month and day.

  11. How to calculate age from date of birth in Excel
  12. 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.

  13. How to calculate age from date of birth in Excel
  14. Other ages will be calculated.

  15. How to calculate age from date of birth in Excel
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.
  1. Type
  2. =TRUNC(YEARFRAC(
    How to calculate age from date of birth in Excel
  3. Click the cell
  4. that contains the date of birth.
    How to calculate age from date of birth in Excel
  5. Type a comma and then click the cell that contains the specific date you want to use.

  6. How to calculate age from date of birth in Excel
  7. Type two closing brackets and then press
  8. Enter.
    How to calculate age from date of birth in Excel
  9. Age will be calculated for the specified date.

  10. How to calculate age from date of birth in Excel
  11. If the specified date is changed, age will be automatically recalculated.

  12. How to calculate age from date of birth in Excel
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!

Statement:
This article is reproduced at:yundongfang.com. If there is any infringement, please contact admin@php.cn delete