Introduction To The DATEDIF FunctionTheDATEDIFfunction computes the difference between two dates in a variety of different intervals, such as the number of years, months, or days between the dates. This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided not to document this function in any other versions.DATEDIFis treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation. Do not confuse theDATEDIFworksheet function with the DateDiffVBA function.The syntax forDATEDIFis as follows:

=DATEDIF(Date1, Date2, Interval)

Where:Date1is the first date,Date2is the second date,Intervalis the interval type to return.

IfDate1is later thanDate2,DATEDIFwill return a#NUM!error. If eitherDate1orDate2is not a valid date,DATEDIFwill return a#VALUEerror.TheIntervalvalue should be one ofIntervalMeaningDescription

mMonthsComplete calendar months between the dates.

dDaysNumber of days between the dates.

yYearsComplete calendar years between the dates.

ymMonths Excluding YearsComplete calendar months between the dates as if they were of the same year.

ydDays Excluding YearsComplete calendar days between the dates as if they were of the same year.

mdDays Excluding Years And MonthsComplete calendar days between the dates as if they were of the same month and same year.

IfIntervalis not one of the items listed in above,DATEDIFwill return a#NUMerror.

If you are including theIntervalstring directly within the formula, you must enclose it in double quotes:

=DATEDIF(Date1,Date2,"m")

If you have the interval in another cell referenced by the formula, that cell shouldnothave quotes around the interval string. For example, with the formula

=DATEDIF(Date1,Date2,A1)

cellA1should containmnot"m".Supressing Zero Value ComponentsIn typical usage to calculate the number of years, months, and days between two dates, you would use a formula such as the following, whereA1is the start date andB1is the end date:

=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")&" days"

This will return a string such as

12 years 8 months 14 days

However, if the number of years and/or months is 0, you'll get a string like

0 years 0 months 14 days

0 years 3 months 14 days

If you want to suppress the 0 values, and return a result such as

8 months 14 daysor14 days

where the 0-valued components are not displayed, use a formula like the following:

=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"

This will display only the date components whose value is greater than 0. The day value will always be displayed, but the year and/or the month value may be suppresed.

ExamplesThe follow are some examples of theDATEDIFfunction.

Date1: 1-Jan-2007Date2: 10-Jan-2007Interval: dResult: 9Explanation:There are 9, not 10, calendar days between these two dates.

Date1: 1-Jan-2007Date2: 31-Jan-2007Interval: mResult: 0Explanation:There 0 complete calendar months between the two dates.

Date1: 1-Jan-2007Date2: 1-Feb-2007Interval: mResult: 1Explanation:There are 1 complete months between the two dates.

Date1: 1-Jan-2007Date2: 28-Feb-2007Interval: mResult: 1Explanation:There are 1 complete months between the two dates.

Date1: 1-Jan-2007Date2: 31-Dec-2007Interval: dResult: 364Explanation:There are 364 days between the two dates.

Date1: 1-Jan-2007Date2: 31-Jan-2007Interval: yResult: 0Explanation:There are 0 complete years between the dates

Date1: 1-Jan-2007Date2: 1-July-2008Interval: dResult: 547Explanation:There are 547 days between the two dates.

Date1: 1-Jan-2007Date2: 1-July-2008Interval: mResult: 18Explanation:There are 18 months between the two dates.

Date1: 1-Jan-2007Date2: 1-July-2008Interval: ymResult: 6Explanation:There are 6 months between the two dates if the dates are considered to have the same year. The year it taken fromDate1, notDate2. This makes a difference when one year is a leap year. Since2007is not a leap year, 29-Feb is not counted. SeeDATEDIF And Leap Yearsbelow.

Date1: 1-Jan-2007Date2: 1-July-2008Interval: ydResult: 181Explanation:There are 181 days between the dates if the dates are considered to have the same year. The year it taken fromDate1, notDate2. This makes a difference when one year is a leap year. Since2007is not a leap year, 29-Feb is not counted. SeeDATEDIF And Leap Yearsbelow.

Date1: 1-Jan-2008Date2: 1-July-2009Interval: ydResult: 182Explanation:There are 182 days between the dates if the dates are considered to have the same year. This result is 182 not 181 sinceDate1is a leap year and thus 29-Feb is counted.

Date1: 1-Jan-2007Date2: 31-Jan-2007Interval: mdResult: 30Explanation:There are 30 days between the 1st and 31st of the dates when both dates' month and years are considered to be the same.

DATEDIF And Leap YearsWhen calculating date intervals,DATEDIFuses the year ofDate1, notDate2when calculating theyd,ymandmdintervals. For example,

=DATEDIF(Date1,Date2,"md")

returns 28 forDate1 = 1-Feb-2007andDate2 = 1-March-2009. SinceDate1is not a leap year, the date 29-Feb is not counted. But the same formula withDate1 = 1-Feb-2008returns29, sinceDate1is a leap year and therefore the date29-Febis counted.

Calculating AgeYou can use theDATEDIFto calculate a person's age. For example, the formula below will calculate the age of a person as of the current date, whereBirthDateis the person's date of birth.

=DATEDIF(BirthDate,TODAY(),"y")&" years"& DATEDIF(BirthDate,TODAY(),"ym")& "months" & DATEDIF(BirthDate,TODAY(),"md")&" days"

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