Formula for calculating age

New Contributor

Looking for a formula to calculate someone's age given their birthday and current date. I am tracking program participants in an Excel worksheet and have their birthdays. I need to know their ages at given points in the year. Is there a formula that I could use to calculate their age every time the worksheet is opened?


Thanks for any advice!

7 Replies


As variant


@AmyMi It depends a bit on how accurate you want to be. You could use a formula like:






Replace birth_date with the cell reference that contains the birthday.

@Sergei Baklan @Riny_van_Eekelen 

Thanks - both ways worked!



An old function dressed up with modern dynamic arrays.

@AmyMi , you are welcome

@Peter Bartholomew 

I like this sample if only "Joe age is 72 years 3 months and 17 days" is practical.

@Sergei Baklan 

It has the air of somewhat spurious accuracy and occupies 3 cells.  Nevertheless, I thought it interesting to show that the formula works.  Maybe

= TEXTJOIN("yrs, ",,
      DATEDIF(DoB, TODAY(), {"y","ym"})

giving "72yrs, 3mths" in a single cell would be more practical.