Jump to content

How to Calculate the Difference Between Dates in Excel using DATEDIF()

0
  sarahkim's Photo
Posted Jul 01 2010 09:01 AM

This excerpt from Excel 2010: The Missing Manual shows you how to use DATEDIF() to calculate the difference between dates in years or months.

If you want to calculate the difference between dates in years or months you could try and break the date up into components using the DAY(), MONTH(), and YEAR() functions, but these types of calculations can get surprisingly complicated. You're better off using Excel's little-known DATEDIF() function. Despite this being a useful gem for many date calculations, Excel's own Help tool neglects to cover this function.

DATEDIF() accepts three arguments. The first two are the dates that you want to find the difference between. The smaller (older) date goes first, and the other date comes second. The third argument is a string that indicates the way you want to measure the interval, such as in days, months, years, and so on. The image below explains your options. Remember, as with all string arguments, you need to put quotation marks around whatever value you choose. The formula looks like this:

DATEDIF(start_date, end_date, interval_type)


Note: Remember, you can't use literal dates in any formula (ever). Instead, use tools like the DATE() function to first transform your date into the type of number you can use inside a formula.

Attached Image

For example, here's how to calculate the number of months that separate a date in the future (stored in cell A1) from the current date:

=DATEDIF(TODAY(), A1, "m")


Remember that when using the DATEDIF() function with the "m" argument, you're counting complete months. That means Excel counts the interval from, say, January 6, 2010 to February 6, 2010, as one month, but the interval from January 6, 2010 to February 5, 2010, is still a day shy, and Excel therefore counts it as zero months. Usually, this is the behavior you want. However, you do have another, somewhat more complicated option, if you want intervals like January 6–February 5 to register as one month: use the YEAR() and MONTH() functions.

For example, here's the DATEDIF() approach (which has a result of 0):

=DATEDIF(DATE(2010,1,6), DATE(2010,2,5), "m")


And here's the YEAR() and MONTH() approach, which has the result of 1 (it works by subtracting the one month number from the other):

=MONTH(DATE(2010,2,5))-MONTH(DATE(2010,1,6))


And here's a revised approach that works with dates that aren't in the same year:

=(YEAR(DATE(2011,2,5))-YEAR(DATE(2010,1,6)))*12 +
MONTH(DATE(2011,2,5))-MONTH(DATE(2010,1,6))


Although this formula looks more complicated at first glance, it's really not that difficult. It's so long because Excel calculates the year and month components separately. Once you find the difference in year numbers, Excel multiplies that number by 12, and then adds it to the month component. You then end up with the total number of months.

Unfortunately, this formula assumes that every year is 365 days, which neglects leap years. This formula is probably right most of the time, but it fails in the days just before or after a person's birthday.

Excel 2010: The Missing Manual

Learn more about this topic from Excel 2010: The Missing Manual.

Fast-paced and easy to use, this guide shows you how to get the most out of Excel 2010. You'll learn how to develop a spreadsheet from scratch, create formulas, add data, and analyze and graph data so you can make informed business decisions. With clear jargon-free explanations, step-by-step instructions, tons of illustrations, and lots of undocumented tips and shortcuts, you'll get hands-on guided tours and explanations of Excel's new features.

See what you'll learn


Tags:
1 Subscribe


2 Replies

0
  alexm262's Photo
Posted Feb 13 2012 11:14 AM

Something is strange with Excel, or maybe with me. I wanted to do a simple thing - to calculate how many days my grandfather lived. He was born on Oct.20, 1893, and passed away on Aug.10, 1981. A simple statement "=DATEDIF(DATE(1893,10,20), DATE(1981,8,10),"d")", doesn't work. Neither does a formula where I place two dates in two different cells and then subtract one from another. It works fine when I calculate my life, or my wife, my mom. All of us were born in 20th century. But as soon as I go below the number 1900, Excel does a strange conversion. For example, 10/20/1893 is immediately turned into 10/20/3793. And as a result, it looks like my grandfather lived -661891 days.

I wonder why!
0
  MJ214's Photo
Posted May 06 2013 11:02 AM

The earliest date allowed for calculation in excel is January 4, 1900