Solved

# excel difference between two dates

Posted on 2010-08-28
434 Views
Ok then a simple question very difficult answer I find...

I have two dates in columns 01/01/2004 and 31/12/2010, and as any sensible person would say this is seven years which equates to 7 * 12 = 84 months.  So why does excel =Datedif(c1,c2,"m") compute 83?  Actually why does excel datedif also compute the above dates as 6 years (=Datedif(c1,c2,"y")).

The crux of the matter is how do I get the true duration (in months) between two dates?

TIA
0
Question by:SMcP
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 6

Expert Comment

ID: 33548854
I expect DateDif is not computing the Actual date, but the months, and as such it is calculating as if you are going from 01/01/2004 to 01/12/2010....83 months.  In the same way it is calculating 6 years 11 months rounded down to 6 years.
0

LVL 6

Accepted Solution

ID: 33548867
Actually this is a know bug.

Someone built a workaround function to take care of it:

http://www.h3.dion.ne.jp/~sakatsu/Excel_Tips05_E.htm

Regards
Steve
0

LVL 2

Assisted Solution

IngCharlie earned 75 total points
ID: 33548885
Use this code
=DATEDIF(A1;B1+1;"m")

And I think the reason is simple is says from >= to < .
If you would just take the month from a date excel would store it as the 1st day of the month.
The same abstraction works with days, so if the second date is not the 1st day of next month this month didn't end.
0

LVL 2

Expert Comment

ID: 33548913
With the years it is still the same. A simple example you become 1 year older on the date of your birth.
So this is not bug, this is just a question what you need to calculate and in your case just use
for months
=DATEDIF(C1;C2+1;"m")
for years:
=DATEDIF(C1;C2+1;"y")

But remember that you will get the same result 84 also comparing the dates till
01/01/2004 and 30/01/2011
and on the 31/01/2011 you will get 85 months
0

LVL 17

Expert Comment

ID: 33548914
Excel is right.

If you had compared 1/1/2004 to 1/1/2011 then you would indeed have 7 years.

That's how I see it as a sensible person :-)
0

LVL 50

Assisted Solution

barry houdini earned 75 total points
ID: 33548931
Not for points
I wouldn't say this was a "bug", it's just the way DATEDIF works. When you calculate the number of days or months between dates you always have to define your method. Is today until tomorrow 1 day or 2? Of course if you mean that to be from the start of today until end of tomorrow it's 2 days.......but surely 10:00 AM today until 10:00 AM tomorrow is 24 hours - 1 day?
DATEDIF uses the latter method, it'll only be a year if its from 1st Jan to 1st Jan......also DATEDIF only counts whole years so if it's short of 7 years, even by a day, it's only 6 years. That's what I do with my age, I'm 46 this year but if somebody asks me how old I am 3 days before my birthday I'll answer "45", wouldn't you?
regards, barry
0

LVL 5

Author Closing Comment

ID: 33669380
None
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month8 days, 15 hours left to enroll