“How do I calculate someone’s age (whole years) if I have their date of birth?” is a common question on my Excel courses. I used to use the following:

We need a cell containing the current date, that updates every day. In this example we’ll use A1. Enter =TODAY() into cell A1.

Type the date of birth into another cell. In this example we’ll use B1.

Enter the following into the cell where you want to display the age: =INT((A1-B1)/365.25)

This subtracts the serial number of the date of birth from the serial number of the current date (if you don’t understand date serial numbers, check out my tutorial on the subject).

The result of this is the number of days between the two dates. To convert days to years, divide the number by 365.25 (number of days in a year – the .25 takes into account leap years).

The INT function returns the integer part of the above calculation (because we only want whole years, not part of a year)

DATEDIF

A much simpler way to calculate the difference between 2 dates is to use the DATEDIF function. You won’t find any information about this function in the help files. It’s not even listed in the Insert Function dialog. According to Microsoft, the function only remains in Excel for backward compatibility.

Using the above scenario, select another empty cell and type the following =DATEDIF(B1,A1,”y”)

This calculates the difference, in whole years, between the date in B1 and the date in A1.

The first date must be earlier than the second date, otherwise an error will be returned.

The 3rd argument should be one of the following:

• d = days. Complete days between the dates.
• m = months. Complete calendar months between the dates.
• y = years. Complete calendar years between the dates.
• ym = Months Excluding Years. Complete calendar months between the dates as if they were of the same year.
• yd = Days Excluding Years. Complete calendar days between the dates as if they were of the same year.
• md = Days Excluding Years And Months. Complete calendar days between the dates as if they were of the same month and same year.