how to calculate age in excel

Copper Contributor

hii everyone, im learning excel from youtube tutorial which uses datedif to calculate age but im not getting that option in my excel. any help would be appreciable.

thank you

7 Replies

@Abhishek_Verma 

 

You might find YEARFRAC to be more suitable. From what I read in this link DATEDIF is a function brought over from Lotus 1-2-3, so though it usually should work, it's not really an Excel function, whereas YEARFRAC is.

 

=INT(YEARFRAC(A1,B1)) will yield the age as an integer.

@Abhishek_Verma 

One gets no help with this deprecated function but

= DATEDIF(dob, today, {"y","ym"})

would still give ages in years and months.  The alternative

= INT(YEARFRAC(+dob, today))

works fine, but it is one of the functions in which the range of birthdates needs to be converted to an array using "+" or "--" for some obscure reason.

@PeterBartholomew1 

The alternative

= INT(YEARFRAC(+dob, today))

works fine, but it is one of the functions in which the range of birthdates needs to be converted to an array using "+" or "--" for some obscure reason.

 

Not in my experience.

 

It didn't need the "+" or "--" when I tested it before replying. See below. [Cell B1 contains =TODAY() ]

mathetes_0-1707949673041.png

 

@mathetes 

As it stands, your formula works fine.  It is

= INT(YEARFRAC(A1:A10, B1))

that becomes a problem.  I have been caught out a number of times by this generation of time functions that appear to be perfectly formed but fail given multi-cell range references.

Got it. Hence your reference to an array!

@Abhishek_Verma 

This works best for me. In my sheet where column 'C' contains DOB.

=INT(YEARFRAC(C5, TODAY()))