One way to calculate age in excel is using the DATEDIF function:

 

Formula: DATEDIF(start_date, end_date, unit)

 

This function returns the difference between two dates in various time units such as years, months and days, depending on the value you supply in the unit argument:

​

“Y” - returns the number of complete years

“M” - returns the number of complete months

“D” - returns the number of days

“YM” - returns months, ignoring days and years.

“MD” - returns the difference in days, ignoring months and years.

“YD” - returns the difference in days, ignoring years.

​

Since we aim to calculate age in years, we are using the "y" unit:

​

Formula: DATEDIF(date of birth, today’s date, “y”)

​

You can used TODAY function to return today’s date for example:

​

​

​

​

​

​

​

​

​

​

​

 

​

​

Formula: =TODAY()

​

Today with empty parenthesis returns todays date and updates each time you open the spreadsheet.

​

Now we can calculate age using the DATEDIF function

​

​

​

​

​

​

​

​

​

​

​

​

​

​

Formula: =DATEDIF(B5,C5,"y")

​

Combine DATEDIF and TODAY function

​

Excel allows us to combine DATED IF and today function, therefore we can calculate Age using one formula

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

Formula: =DATEDIF(B5,TODAY(),"y")

​

 

Below is a short video tutorial on how to combine the two functions and effectively use DatedIF and TODAY() functions in Excel.