SMcP
asked on
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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 :-)
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 :-)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
None