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. If you don't feel like dragging out a calendar, a simple spreadsheet formula can come to the rescue. Alternatively, use the function to compute the difference between any two dates.

Calculate an Age with the DATEDIF Function

Screenshot of Excel showing age calculation

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"

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.

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.

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 between the two dates. 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

Screenshot of Excel showing the entire age formula

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

Screenshot of Excel showing DATEDIF function being entered

This example of the DATEDIF function can help you to calculate your current age in Excel or Google Sheets:

= DATEDIF (E1, TODAY( ),"Y") & " Years, " & DATEDIF (E1, TODAY(),"YM") & " Months, "& DATEDIF (E1, TODAY( ),"MD") & " Days"
  1. Enter your birth date into cell E1 of the worksheet
  2. Type the above formula into cell E3.
  3. Press the ENTER key on the keyboard.
  4. Your current age should appear in cell E3 of the worksheet.