SOLVED

Age calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-266351%22%20slang%3D%22en-US%22%3EAge%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266351%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20dates%20of%20birth%20from%20a%20membership%20spreadsheet.%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20to%20give%20me%20ages%20of%20members%20against%20current%20date%3F%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-266351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266534%22%20slang%3D%22en-US%22%3ERe%3A%20Age%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266534%22%20slang%3D%22en-US%22%3E%3CP%3EThanks!%20Works%20a%20treat!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266532%22%20slang%3D%22en-US%22%3ERe%3A%20Age%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266532%22%20slang%3D%22en-US%22%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266409%22%20slang%3D%22en-US%22%3ERe%3A%20Age%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266409%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Richard%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FDATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDATEDIF%3C%2FA%3E%20function%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DDATEDIF(A1%2CTODAY()%2C%22Y%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3BWhere%20cell%20A1%20is%20the%20birthdate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266381%22%20slang%3D%22en-US%22%3ERe%3A%20Age%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266381%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Richard%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20this%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fget-age-from-birthday%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fget-age-from-birthday%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1982318%22%20slang%3D%22de-DE%22%3ERe%3A%20Age%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1982318%22%20slang%3D%22de-DE%22%3EThis%20is%20fine%2C%20but%20only%20when%20the%20date%20is%20after%201904%20%3CBR%20%2F%3E%20what%20can%20we%20do%20then%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1982319%22%20slang%3D%22en-US%22%3ERe%3A%20Age%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1982319%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F899759%22%20target%3D%22_blank%22%3E%40NickBigNick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Excel%20dates%20start%20from%20Jan%2001%2C%201900%20(Windows)%20or%201904%20(Mac).%20To%20work%20with%20earlier%20dates%20please%20check%20workarounds%20here%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Fcalculate-age-before-1-1-1900%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20to%20calculate%20ages%20before%201%2F1%2F1900%20in%20Excel%20-%20Office%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fpre-1900-dates%2Fm-p%2F129999%22%20target%3D%22_blank%22%3Epre-1900%20dates%20-%20Microsoft%20Tech%20Community%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceluser.com%2F1057%2Fhow-to-work-with-dates-before-1900-in-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EHow%20to%20Work%20with%20Dates%20Before%201900%20in%20Excel%20-%20ExcelUser.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

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

Hi Richard,

 

Please check this https://exceljet.net/formula/get-age-from-birthday

Hi Richard,

 

You can use the DATEDIF function as follows:

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

 Where cell A1 is the birthdate.

 

Hope that helps

Thanks!

Thanks! Works a treat!

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

@NickBigNick 

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

@Haytham Amairah 

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

Thanks

@dolive13 

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

 

@dolive13 

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.

@JMennegke 

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

image.png

@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?

@BBacon621 

That could be

=DATEDIF( DOB, TODAY(), "m")
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

@BBacon621 

=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!