Calculate Age in Excel

Using a combination of built-in functions in Excel, you can quickly calculate a person’s age. There are a couple different ways this method can be used, and we’ll go through both below.

[ad#in-post-ad]

Functions Used in This Calculation
To reach our goal of calculating a person’s age as of a certain date, we will use a combination of formulas.  First, we will be using the INT function.  The INT function takes a number and rounds it down to the nearest whole number.  Therefore if you use INT(10.25), it will return 10.

The other functions  you want to use in this calculation are both date functions.  The TODAY function returns the current date.  And the DATE function builds a date from the year, month and day (which looks like this: DATE(yyyy, mm,dd)).

Formula to Calculate Age
Below are a few ways to calculate a person’s age:

• 2 Dates: =INT((DATE(2011,8,2) – DATE(2001,1,1))/365.25) returns 10.
• Age in the past, 2 dates:  =INT((DATE(2005,8,2) – DATE(2001,1,1))/365.25) returns 4.
• Current Date:  =INT((TODAY() – DATE(2001,1,1))/365.25) returns 10.

You can also use cell references to replace any of the dates in the formuls above (the formula would look like this:  =INT((A1- B1)/365.25), where both A1 and B1 are dates in Excel.

Notice that we use 365.25 to take into account leap year occurring every 4 years in our formula.

More Excel Tips

• Date Formulas in Excel