Excel Function DATEDIF - Calculate the difference between two dates

DATEDIF is an Excel worksheet function which is undocumented  in the Excel help file. For some reason Microsoft has decided not to document this function in any other versions other than Excel 2000! This function is quite usefull if you need to return the diference between two days in years, months or days.

Syntax

DATEDIF(Start_date, End_date, "Interval")

Parameters

Start_date -  A date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")).

End_date - A date that represents the last, or ending, date of the period.

Interval is the interval type you want returned. The Interval value should be one of:

Unit	Returns
"Y"	Number of complete years between the dates.
"M"	Number of complete months between the dates.
"D"	Number of days between the dates.
"MD"	Number of complete days between the dates as if they were of the same month and same year.
"YM"	Number of complete months between the dates as if they were of the same year.
"YD"	Number of complete days between the dates as if they were of the same year.

Some remarks about DATEFDIF function

• If Start_Date if greater than End_date, DATEDIF will retunr a #NUM error.
• If either Start_date or End_date is not a valid date, DATEDIF will return a #VALUE error.
• It will also return a #NUM error if the ‘Interval’ is invalid.

Some Examples of DATEDIF function:

Start_date	End_date	Formula	Description (Result)
15-04-2011	25-02-2014	=DATEDIF(Start_date,End_date,"Y")	Two complete years in the period (2)
06-08-2009	10-12-2014	=DATEDIF(Start_date,End_date,"D")	(1952) days between August 6, 2009, and December 10, 2014
03-03-2014	15-10-2014	=DATEDIF(Start_date,End_date,"MD")	The difference between 3 and 15 = (12) — the day of start_date and the day of end_date — ignoring the months and the years of the dates
01-06-2012	15-08-2013	=DATEDIF(Start_date,End_date,"YD")	(75) days between June 1 and August 15, ignoring the years of the dates.
15-04-2011	25-02-2014	=DATEDIF(Start_date,End_date,"M")	Number of months between April, 2011 and February 2014 — (34)

Formula to calculate your age in years, months and days.

Replace  12/01/1968 by your birth date and the following formula will return your age in Years, Months, Days:

=DATEDIF("12/01/1968",TODAY(),"y")&" years,"& DATEDIF("12/01/1968",TODAY(),"ym") &" month(s), "& DATEDIF("12/01/1968",TODAY(),"md")&" Days"

The need to supress zero values:

=IF(DATEDIF("12/01/1968",TODAY(),"y") = 0, "", DATEDIF("12/01/1968",TODAY(),"y") &" years,")& IF(DATEDIF("12/12/2012",TODAY(),"ym") = 0, "", DATEDIF("12/01/1968",TODAY(),"ym") &" month(s), ")& IF(DATEDIF("12/01/1968",TODAY(),"md") = 0,"",DATEDIF("12/01/1968",TODAY(),"md")&" Days")