“Hidden” Excel Function DATEDIF

Problem: How to calculate the difference between two dates in days, weeks, months or years.

Solution & Explanation:

DATEDIF is one of very few undocumented functions in Excel, you can’t find it on the Formula tab. Try typing =DATEDIF into Excel and it doesn’t recognize it. That is why it's important to know the complete syntax of Excel DATEDIF to be able to use it in your formulas.

DATEDIF syntax: =DATEDIF(Date1,Date2,Interval)

Date1 is the start date of the period you want to calculate

Date2 is the end date of the period

Interval is the type of interval you want to calculate e.g. days, months, years.

DATEDIF Intervals

Interval	Explanation	Example
d	Number of complete days between two dates	=DATEDIF(A2,B2,"d")
m	Number of complete months between two dates	=DATEDIF(A2,B2,"m")
y	Number of complete years between two dates	=DATEDIF(A2,B2,"y")
yd	The date difference in days, ignoring years	=DATEDIF(A2,B2,"yd")
ym	The date difference in months, ignoring days and years	=DATEDIF(A2,B2,"ym")
md	The date difference in days, ignoring months and years	=DATEDIF(A2,B2,"md")

EXAMPLE 

Where we can use DATEDIF:

·         Employee’s (or equipment) age in days, months and years.

·         Length of service of an employee (or equipment).

DATEDIF Errors

·         If Date 1 is later than Date 2                                               => #NUM error.

·         If Date 1 or Date 2 is not a valid date                                  => #VALUE error.

·         If the interval is not one of the above options                      => #NUM error.