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.

Note: The instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

Calculate an Age with the DATEDIF Function

In the following formula, the DATEDIF function determines 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, a person's birth date is entered into cell E1 of a worksheet (see the example below). The cell reference to this location is entered into the formula. If the birth date is stored in a different cell in the worksheet, the three cell references in the formula need to be changed.

Screenshot of Excel showing age calculation

The formula uses DATEDIF three times 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"

Concatenate 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 used in a single formula. For example, the ampersand joins the DATEDIF function to the text Years, Months, and Days in the three sections of the formula.

Screenshot of Excel showing the entire age formula

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 every time a worksheet is recalculated.

Worksheets recalculate each time they are opened. The person's current age increases when the worksheet is opened unless automatic recalculation is turned off.

Example: Calculate Your Current Age with DATEDIF

This example of the DATEDIF function calculates your current age:

  1. Enter your birth date into cell E1 of a blank worksheet.

  2. Enter the formula into cell E3:

    =DATEDIF(E1,TODAY(),"Y")&" Years, "&DATEDIF(E1,TODAY(),"YM")&" Months, "&DATEDIF(E1,TODAY(),"MD")&" Days"
  3. Press ENTER.

    Screenshot of Excel showing DATEDIF function being entered
  4. Your current age appears in cell E3 of the worksheet.