Calculate Your Current Age with DATEDIF
One use for Excel's DATEDIF function is to calculate a person's current age.
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 to 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 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.
The DATEDIF Function's Syntax and Arguments
Example: Calculate Your Current Age with DATEDIF
- Enter your birth date into cell E1 of the worksheet
- 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
- Type the following formula into cell E3
- Press the ENTER key on the keyboard
- Your current age should appear in cell E3 of the worksheet.
- When you click on cell E3 the complete function appears in the formula bar above the worksheet
=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" .