Search results for

All search results
Best daily deals

Affiliate links on Android Authority may earn us a commission. Learn more.

How to calculate age in Excel

Curious how old someone is and too afraid to ask them? Here's a trick to find out.
By

Published onFebruary 16, 2023

Finding the exact difference between two dates in Excel may not be the most used feature, but it can be a lot of fun. A classic example is working out someone’s age. It’s not as complicated as it sounds, and there’s a handy function in Excel that can give you an exact age to the day. Read on for tips on how to calculate age in Excel.

Read more: How to add cells in Excel

QUICK ANSWER

To calculate someone's age in Excel, enter the formula =DATEDIF(A2,TODAY()," y"), where cell A2 contains the date they were born and "Today" is today's date. Press Enter, and Excel will output the age.

How to calculate age in Excel

This is a pretty simple three-column task. To get started, enter the date of a person’s birth into your first cell. In our example, we’re using Tom Hanks, whose date of birth is in cell A2.

Now, enter today’s date into the cell next to your first cell. It’s just easier to keep both dates together if you’re tracking multiple ages.

calculate age in excel 1 1200x332 1
Adam Birney / Android Authority

In the third cell (for us, it’s C2), enter the following formula:

Code
=DATEDIF(A2, B2, "y").

The two cell identifiers are relatively straightforward, and using the letter y as the third indicator means that you’re only interested in the number of years.

calculate age in excel 2 1200x335 1
Adam Birney / Android Authority

You can also get a person’s age without entering today’s date in the second cell. To do this, change your formula to =DATEDIF(A2,TODAY(),” y”). Excel will do the rest by identifying the date and calculating the age.

If you really want to get specific, you can also calculate a person’s age on a particular historical date. We’re going to get historical with our example and calculate the exact age of Tom Hanks when the film Castaway was released: December 7, 2000.

The formula should look like this:

Code
=DATEDIF(A2, DATE(2000,12,7), "y")

Note that the date format is year, month, and day.

calculate age in excel 3 1200x334 1
Adam Birney / Android Authority

The final, most specific measurement you can make is a person’s age, including months and days. The formula takes a little longer than previous measurements, but the process remains the same. Your formula should look like this:

Code
=DATEDIF(A2,B2, “y”) & “y” & DATEDIF(A2, B2, “ym”) & “m” & DATEDIF(A2,B2, “md”) & “d.”
calculate age in excel 4 1200x337 1
Adam Birney / Android Authority

While it looks like a mouthful, it makes more sense once you break it down. You need to have a DATEDIF function for each level of measurement, which means that the first function indicates years. The second function measures the years and months, but the “m” ensures that it only displays the month. The final function calculates the differences in months and days without the years and shows the date with “d.”

Now you know how to calculate age in Excel. Please only use your powers for good.


Read more: How to measure distance on Google Maps

FAQs

If you want to calculate what someone’s age was on a specific date, either in the past or in the future, then modify the formula to be =DATEDIF(XX, “MM/DD/YYYY,” “Y”)

If you want to calculate what someone’s age was on a specific date, either in the past or in the future, then modify the formula to be =DATEDIF(XX, “MM/DD/YYYY,” “Y”)

You can calculate age in Excel without using the DATEDIF function by using the following formula:

=INT((TODAY()-birthdate)/365.25)

The formula works by subtracting the birthdate from today’s date, then dividing the result by 365.25 to account for leap years. The INT function is used to round down the result to the nearest whole number, which represents the person’s age.

You might like