 SOLVED

# Age calculation

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?

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

Hi Richard,

# Re: Age calculation

Hi Richard,

You can use the DATEDIF function as follows:

`=DATEDIF(A1,TODAY(),"Y")`

Where cell A1 is the birthdate.

Hope that helps

Thanks!

# Re: Age calculation

Thanks! Works a treat!

# Re: Age calculation

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

# Re: Age calculation

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 - ExcelUser.com

# Re: Age calculation

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

Thanks

# Re: Age calculation

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

# Re: Age calculation

@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

# Re: Age calculation

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?

# Re: Age calculation

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

# Re: Age calculation

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 # Re: Age calculation

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?

# Re: Age calculation

That could be

``=DATEDIF( DOB, TODAY(), "m")``

# Re: Age calculation

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)
thanks

# Re: Age calculation

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

# Re: Age calculation

OK - THANKS - I figured out my mistake - it works now..... THANK YOU!

# Re: Age calculation

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?

Thanks

--B