Use DATEDIF function in getting Age

[IMA_Saihat]

Hi,

DATEDIF function is a useful Excel function in calculating difference between dates; such as getting Age in the following formula:

=DATEDIF(A2;TODAY();"y")&" Year, "&DATEDIF(A2;TODAY();"ym")&" Month, "&DATEDIF(A2;TODAY();"md")&" day"

[Nisha Dhawan]

Hi,

If you are getting any error in this formula please use below formula:-
=DATEDIF(A2,TODAY(),"y")&" Year, "&DATEDIF(A2,TODAY(),"ym")&" Month, "&DATEDIF(A2,TODAY(),"md")&" day"

Thanks
Nisha

[FDibbins]

In case anyone did not notice the difference, post 1 contains ; while post 2 contains ,
These would be for different regional settings

[IMA_Saihat]

Hi,
Nisha Dhawan
FDibbins

Thank you for Clarification of difference between ( and (,)"this is applicable to all Excel Formulas".

Regards

[IMA_Saihat]

Hi,

Please note the following:
• DATEDIF is a hidden function in Excel. As the name suggests the job of this function is to calculate the difference between two given dates.
• I have referred this function as hidden because, for some reason Microsoft has decided not to document this function. Moreover, because of this you will not find this function in the Formula Tab.
• To verify this, try to type =DATE in any cell. You will see Excel enlists all the functions that start with the word “date” but it does not shows DATEDIF.
• DATEDIF Function in Excel is very different from the DATEDIFF (Notice the extra ‘F’) Function in VBA.

[:) Sixthsense :)]

DATEDIF is a hidden function in Excel,I have referred this function as hidden because, for some reason Microsoft has decided not to document this function

B'cos DatedIf() is not an Inbuilt worksheet function. It is an addin function which resides in Analysis Toolpak

[Tony Valko]

B'cos DatedIf() is not an Inbuilt worksheet function. It is an addin function which resides in Analysis Toolpak

This link lists all the ATP functions:

https://support2.microsoft.com/defau...b;EN-US;291058

Scroll down near the bottom of the page.

[Tony Valko]

Just for the heck of it...

Here's a version that uses grammatically correct forms of plurals.

For example, you could get a result like this:

1 Years 1 Months 1 Days

That just doesn't roll off the tongue like it should!

This formula will return:

1 Year 1 Month 1 Day

A1 = date of birth
B1 = today's date

=DATEDIF(A1,B1,"y")&" year"&IF(DATEDIF(A1,B1,"y")<>1,"s ","")
&" "&DATEDIF(A1,B1,"ym")&" month"&IF(DATEDIF(A1,B1,"ym")
<>1,"s ","")&" "&DATEDIF(A1,B1,"md")&" day"&IF(DATEDIF(A1,B1,"md")
<>1,"s","")

[Tony Valko]

For more info see this:

http://www.cpearson.com/Excel/datedif.aspx

[:) Sixthsense :)]

This link lists all the ATP functions:

Thanks for the correction

I should check it before commenting

[Tony Valko]

In Excel 2003 and earlier with the Analysis ToolPak add-in installed, it's easy to differentiate the ATP functions from the built-in functions.

If you have Function ToolTips enabled, when you start typing in a built-in function the Function ToolTip appears. For example,

=count(

Then the tooltip appears as:

=count(
COUNT(value1,[value2], ...)

The DATEDIF function has a tooltip but it doesn't provide any arguments:

=datedif(
DATEDIF()

ATP functions don't have tooltips:

=networkdays(

That's all you get!

However, you can get the ATP and UDF function arguments by typing in the function:

=networkdays(

At that point use the key combo of CTRL + SHIFT + A and the function arguments will appear:

=networkdays(start_date,end_date,holidays)