Most people want to calculate the number of months between two dates but it is very difficult to find documentation on the DATEIF function.  Thank goodness I found the information under a site called My Online Training Hub (Mynda, Co-Founder)

DATEDIF syntax

=DATEDIF(date1,date2,interval)

Date1 is the start date

Date2 is the end date

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

DATEDIF Intervals

d = days. Complete days between two dates.

m = months. Complete calendar months between two dates.

y = years. Complete calendar years between two dates.

yd = Complete calendar days between two dates as though the end date is in the same year as the start date.

ym = Complete calendar months between two dates as though the end date is in the same year as the start date.

md = complete calendar days between two dates as though the month and year of the end date is the same as the start date.

DATEDIF examples:

Ideas for Using DATEDIF:

  • Your age in days, months and years.
  • Length of service of an employee.
  • Equipment age.
  • Countdown to a date.

DATEDIF Errors

  1. If Date 1 is later than Date 2 Excel will return a #NUM error.
  2. If Date 1 or Date 2 is not a valid date you will get a #VALUE error.
  3. If the interval is not one of the above options Excel will return a #NUM error.
Advertisements