How to get Age from Date of Birth in Excel? – Formula & VBA

How to Get Age from Date of Birth (DOB)?

The very easiest way to calculate person’s age from using their birthday in Excel is using this formula.

Syntax: DATEDIF([start-date],[end-date],[return type])

To calculate person’s age using datedif function follow these steps.

  1. Open a Excel workbook.
  2. In cell A1 – Enter person’s date of birth.
  3. In cell B1, Type this formula: =DATEDIF(A1,TODAY(),”Y”)
  4. Press Enter to get the age of the person.

Excel – Age Calculation with Reference Date

If you do not want to use the  TODAY() function you may use reference to a call with a date on which you would like to calculate the age as given below:

  • A1 – the cell where you have person’s birthrate
  • B1 – the date when you want to calculate teh age
  • Enter the formula where you want to output the age:
  • =DATEDIF(A1,B1,”Y”)

Calculate Age – Years, Months & Days

To calculate accurate age updato days, we find number of years, then the number of months, and then the number of days.

VBA: To use the same function in vba, use Application.worksheetfunctions.DateDif

Type the following formula in cells A3:

=DATEDIF (A1, TODAY( ),"Y") & " Years, " & DATEDIF (A1, TODAY( ),"YM") & " Months, "
& DATEDIF (A1, TODAY( ),"MD") & " Days"

The three parts of the formula are:

  1. Number of Years: DATEDIF (A1, TODAY( ), “Y”) & ” Years “
  2. Number of Months: DATEDIF (A1, TODAY( ), “YM”) & ” Months “
  3. Number of Days: DATEDIF (A1, TODAY( ), “MD”) & ” Days”

Enter your birth date into cell A1 of the worksheet.

Type =TODAY( ) into cell B1. (Optional). Displays the current system date. This is for your reference only, this data is not used by the DATEDIF formula above.

Note: When entering text data into a formula it must be enclosed in double quotation marks such as ” Years.”

Press the ENTER key on the keyboard .

Your current age should appear in cell A3 of the worksheet.

Leave a Reply