Calculating a Persons Age in Excel. So you want to know a person’s age or need to calculate how many years have lapsed since a renewal date. Perhaps you want to know the ages of your staff without having to calculate it each year or you want to check if one of your students is old enough to sit an exam. There are many situations where we need to calculate a year difference from two dates and it is a little trickier than it first appears.

Subtracting two dates

Excel is pretty handy with dates – so you might think that we could just subtract the two dates something like this:

=”23/05/2014″-“01/07/2014”

As you would expect – Excel will return the number of days between the two dates, very useful but not quite what we want. First thoughts would be to divide this number by 365 and round to the nearest whole-number – but that won’t always work.

Leap years

Leap years introduce a special problem for us – as we can’t be sure if the number of days in the year is 365 or 366. One option might be to divide the number of days by 365.25 – adding a .25 to account for the extra day every four years.

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

This is quite a common approach and this may prove sufficient for some general statistical purposes – but in many other situations it will introduce errors. We need a more precise method – and Excel has already provided one for us in the form of the DATEDIF function.

The DATEDIF Function

The DATEDIF function takes two dates and displays the difference between them in Years, Months or Days dependent on the format chosen. The function is clever and properly deals with leap years. Let’s try it. Insert the two dates into separate cells. In our example we are inserting them into cells B1 and B2.

Inserting the current date

We can use the TODAY() function in Excel to display the current date in a cell – this will update automatically whenever the date changes and whenever Excel is opened.

• To insert today’s date we use the Today function TODAY()

• Press OK.

A message will pop up telling you that the function will insert today’s date.

• Press OK. Today’s date will appear in cell B2.

This will update automatically every time we open the workbook or recalculate the sheet.

Next we’ll insert our initial date or Birth Date

• Let’s put in a date of birth 23/5/197 into cell B1.

Next into cell B5 we’ll insert the DATEDIF function

• Type the formulae =DATEDIF(B1, B2, “Y”)

We have now instructed Excel to work out the interval of time between those dates in cells B1 and B2. The third parameter “Y” tells Excel we want to see the interval in whole Years.

• To calculate years between the dates we use “y”
• To calculate all the months between the dates we use “m”
• To calculate all the days between the dates we use “d”.

So if we want to know the number of years between the two dates we use ‘y’. This gives us 44 years.

Getting more precise birth date information

In addition to the number of years we may wish to know the number of months and days between two dates. Specifying the format “ym” will display the number of months excluding years in the difference between two dates. Specifying the format “md” will calculate the number of days excluding months. Using these different formats we can expand our

Displaying a person’s age in one cell correct to the day.

Our basic formulas for working out the difference in years, months and days look like this

• Years        =DATEIF(B1,B2,”y”)
• Months    =DATEDIF(B1,B2,”ym”)
• Days         =DATEDIF(B1,B2,”md”)

To show the calculation of years, months, days in one cell we concatenate these functions together.

Our single formula to show the person’s age in years, months and days:

• =DATEDIF(B1,B2,”y”) & ” Years, ” & DATEDIF(B1,B2,”ym”) & ” Months and ” & DATEDIF(B1,B2,”md”) & ” Days”

For our example above this will display

44 years 2 Months and 7 Days