Calculate Your Current Age with the Excel DATEDIF Function

Need to know your age (or someone else's?)

Old and young hands
Tim Flach / Getty Images

 One use for Excel's DATEDIF function is to calculate a person's current age. This is helpful in a wide variety of situations.

Calculate Your Current Age with DATEDIF

Calculate Your Current Age with the Excel DATEDIF Function.

In the following formula, the DATEDIF function is used to determine a person's current age in years, months and days.

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

Note: To make the formula easier to work with, the person's birth date is entered into cell E1 of the worksheet. The cell reference to this location is then entered into the formula.

If you have the birth date stored in a different cell in the worksheet, be sure to change the three cell references in the formula.

Breaking Down the Formula

Click on the image above to enlarge it

The formula uses DATEDIF three times in the formula to calculate first the number of years, then the number of months, and then the number of days.

The three parts of the formula are:

Number of Years: DATEDIF (E1, TODAY( ), "Y") & " Years "
Number of Months: DATEDIF (E1, TODAY( ), "YM") & " Months "
Number of Days: DATEDIF (E1, TODAY( ), "MD") & " Days"

Concatenating the Formula Together

The ampersand ( & ) is a concatenation symbol in Excel.

One use for concatenation is to join number data and text data together when they are used together in a single formula.

For example, the ampersand is used to join the DATEDIF function to the text "Years", "Months", and "Days" in the three sections of the formula shown above.

The TODAY( ) Function

The formula also makes use of the TODAY( ) function to enter the current date into the DATEDIF formula.

Since the TODAY( ) function uses the computer's serial date to find the current date, the function continually updates itself every time a worksheet is recalculated.

Normally worksheets recalculate each time they are opened so the person's current age will increase every day that the worksheet is opened unless automatic recalculation is turned off.

Example: Calculate Your Current Age with DATEDIF

  1. Enter your birth date into cell E1 of the worksheet
  2. Type =TODAY( ) into cell E2. (Optional). Displays the current date as seen in the image above, This is for your reference only, this data is not used by the DATEDIF formula below
  3. Type the following formula into cell E3
  4. =DATEDIF (E1, TODAY( ),"Y") & " Years, " & DATEDIF (E1, TODAY( ),"YM") & " Months, "
    & DATEDIF (E1, TODAY( ),"MD") & " Days"

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

  5. Press the ENTER key on the keyboard
  6. Your current age should appear in cell E3 of the worksheet.
  7. When you click on cell E3 the complete function appears in the formula bar above the worksheet