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

@AmyMi 

As variant

=YEARFRAC( DOB, TODAY() )

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

 

=(TODAY()-birth_date)/365

or

=YEARFRAC(birth_date,TODAY())

 

Replace birth_date with the cell reference that contains the birthday.

@Sergei Baklan @Riny_van_Eekelen 

Thanks - both ways worked!

@AmyMi 

image.png

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"})
  )&"mths"

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