Age calculation

New Contributor

I have a list of dates of birth from a membership spreadsheet.

Is there a formula to give me ages of members against current date?

Thanks in advance.

23 Replies
best response confirmed by Richard Kirk (New Contributor)

Hi Richard,


Please check this

Hi Richard,


You can use the DATEDIF function as follows:


 Where cell A1 is the birthdate.


Hope that helps


Thanks! Works a treat!

This is fine, but only when the date is after 1904
what can we do then ?


In Excel dates start from Jan 01, 1900 (Windows) or 1904 (Mac). To work with earlier dates please check workarounds here

How to calculate ages before 1/1/1900 in Excel - Office | Microsoft Docs

pre-1900 dates - Microsoft Tech Community

How to Work with Dates Before 1900 in Excel -

@Haytham Amairah 

This format did not work for me. Do you know why that would be?



Could you share screenshot or sample file, and what exactly means "not work"?

@Sergei Baklan  I had existing data ranges filled out with birth dates in them. I wanted the year first and tried formatting this way but the changes never occurred. After giving up on that I still wanted to figure out current age and since I will be looking at this file for years to come I would like it to update automatically.  What function do I use to do this? Thanks



As variant birthdays are entered as texts which looks as dates, not as values which Excel recognize as dates for your regional settings. Whatever. Why don't you share small sample file?

Hi Sergei
My Excel doesn't have the datedif function on fx.


Function is not mentioned in fx list and if you type it in the cell you see no prompt till first bracket. After that also not very informative, but at least something


@Haytham Amairah 

Do you have a formula that would calculate age in months?  i.e. infants for a daycare spreadsheet based on DOB and today's date?


That could be

thank you - it "sorta worked" I wanted the formula to show me in one cell for example:
18 months should show as 1 year, 6 months (if it's possible)


=DATEDIF(DOB,TODAY(),"y")&" year(s), "&MOD(DATEDIF(DOB,TODAY(),"m"),12)&" month(s)"

I get the dreaded #NAME?
OK - THANKS - I figured out my mistake - it works now..... THANK YOU!



I have also encountered the ?NAME error. When I type in the formula, it prompts me for the correct info; but I cannot get it to return results. Has anyone found a solution for this?