excel difference between two dates

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
LVL 5
SMcPAsked:
Who is Participating?
 
radnbneConnect With a Mentor Commented:
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
 
radnbneCommented:
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
 
IngCharlieConnect With a Mentor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
IngCharlieCommented:
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
 
calacucciaCommented:
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
 
barry houdiniConnect With a Mentor Commented:
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
 
SMcPAuthor Commented:
None
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.