Feb 16 2018

Using The Microsoft Excel DATEDIF Function

 

Using The Microsoft Excel DATEDIF Function

 

Do you want to know the number of seconds, minutes, hours, days, weeks, month or years between two dates? If so, there are several ways to calculate it. One of the easiest is just to use the DateFif function in Excel. But many people have never heard of the DateDif function. We use it, so here it is.

 

The DATEDIF Excel function is a mystery to many people because it doesn’t have the popup help that the other functions have: Microsoft describes it like this “Calculates the number of days, months, or years between two dates. Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios.” The “MD” argument may result in a negative number, a zero, or an inaccurate result. If you are trying to calculate the remaining days after the last completed month.

 

microsoft-excel-datediff

 

What is the use of Datedif function in Excel?
The Microsoft Excel DATEDIF function returns the difference between two date values, based on the interval specified. The DATEDIF function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel.

 

So it’s a leftover from a long time ago. And it’s still useful. Because Excel stores dates as serial numbers, actually the number of days since January 1, 1900, you can subtract one date from another and get the number of days between them. Besides Days, DATEDIF will convert the result into Years, Months, the difference in the Day part of the date, the difference in the Month part of the date and the difference in the Year part of the date.

 

datediff-microsoft-excel

 

So what to do? If it’s a quick, easy model and you’re not using the ‘MD’ argument, use DATEDIF. For longer term models where you might not know all the dates that are going to be used, use native Excel formulas like this:

 

datediff

 

  • Start Date 1/1/17
  • End Date 3/1/18
  • Results
  • DATEDIF Formula Formula Detail
  • Y 1 1 =YEAR($B$2)-YEAR($B$1)
  • M 14 14 =((YEAR($B$2)-YEAR($B$1))*12)+(MONTH($B$2)-MONTH($B$1))
  • D 424 424 =$B$2-$B$1
  • MD 0 0 =MOD(DAY($B$2)-DAY($B$1),DAY(EOMONTH($B$1,0)))
  • YM 2 2 =MOD(MONTH($B$2)-MONTH($B$1),12)
  • YD 59 59 =MOD($B$2-DATE(YEAR($B$2),MONTH($B$1),DAY($B$1)),365)

 
 

How do you calculate the time between two fields? If you have not used Datedif will you now?

 
 
 

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS:

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives