How to use Microsoft Excel Date Difference Function

Unbeknownst to many people, all versions of Microsoft Excel have a worksheet function for calculating the difference between two dates: DATEDIF. For whatever reason, it's undocumented in most versions of Excel. It's already there, and you don't have to do anything special to access it!

Difficulty: Easy

Instructions

Things You'll Need:

• Microsoft Excel

1. You need to know the syntax for the Microsoft Excel DATEDIF function:
   
   =DATEDIF(Date1, Date2, Interval)

2. Use Excel cell references to the cells containing your dates to replace Date1 and Date2.

3. Choose one of the following values for the interval argument in the DATEDIF function:
   
   y - number of complete years.
   m - number of complete months.
   d - number of days.
   md - difference between the days (months and years are ignored).
   ym - difference between the months (days and years are ignored).
   yd - difference between the days (years and dates are ignored).

4. Surround the interval value with quotes when you enter it into the your formula.

5. A complete example of the Excel DATEDIF in use:
   
   =DATEDIF(A2, B2, "m")
   
   In this is example DATEDIF will return the difference between the dates in A2 and B2, in complete months.

6. Try it for yourself. Type two dates into two cells in Excel - make sure they're over a year apart to get the most from this exercise. Enter the DATEDIF formula in another cell. Start with "y" and work your way through all of the intervals. You'll understand the function a lot better by doing this!

Tips & Warnings

• If Date2 is earlier than Date1 you will get an error: #NUM! in your cell.

[odoli]

on 2/1/2010 Thank you; this just helped me at work.

[soanyway]

on 3/6/2009 Wow! thank you for this tidbit! I love excell and did not know this!

[healthymomof5]

on 2/2/2009 Very informative!5*