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
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.
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
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
MONTH()approach, which has the result of 1 (it works by subtracting the one month number from the other):
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.
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.