Excel Function: DATEDIF (Explaining DATEDIF function with Images.)

The DATEDIF function calculates the difference between two dates in number of ways, such as years, months, or days between the entered dates. You will not find this function in Formulas tab but it there in excel. To use, you’ll have to type it manually in any cell. The syntax for DATEDIF is as follows:

=DATEDIF(DateA, DateB, Interval)

Where:
DateA is the first date,
DateB is the second date,
Interval is the interval type to return.

The Interval value should be one of the following:

• "d" means Days (Number of days between the dates.)
• "m" means Months (Complete calendar months between the dates.)
• "y" means Years (Complete calendar years between the dates.)
• "ym" means Months Excluding Years (Complete calendar months between the dates as if they were of the same year.)
• "yd" means Days Excluding Years (Complete calendar days between the dates as if they were of the same year.)
• "md" means Days Excluding Years And Months (Complete calendar days between the dates as if they were of the same month and same year.)

The Interval should be within “_”double quotes when Interval string is being entered directly within the formula:

=DATEDIF(DateA,DateB,"d")

When you have interval in another cell referenced in the formula, that cell should not have double quotes around the Interval string. For ex,

=DATEDIF(DateA,DateB,C1)

cell C1 should contain d not "d".
 

Best Usage: Calculating Age
When you have to calculate your age as of today DATEDIF is of great use. It not simplifies the process and provide presentable figure. For example, formula below will give result in cell B2 the age of a person as of the current date, where cell A1 contains person's date of birth.

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

We sometime need to know exact age as on any specific date, commonly while applying for government jobs which has a upper limit for age as a eligibility criteria. DATEDIF function can also be used to ease out the problem as follows:

C2: =DATEDIF(A2,B2,"y")&" years "&DATEDIF(A2,B2,"ym")&" months "&DATEDIF(A2,B2,"md")&" days"

That’s it for now, will post more soon. Comments and questions are welcome. Till then, “Learn & Grow Together with EXCEL”