Solved

excel difference between two dates

Posted on 2010-08-28
7
433 Views
Last Modified: 2012-05-10
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
Comment
Question by:SMcP
7 Comments
 
LVL 6

Expert Comment

by:radnbne
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

by:
radnbne earned 100 total points
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

by:IngCharlie
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 2

Expert Comment

by:IngCharlie
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

by:calacuccia
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

by:barry houdini
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

by:SMcP
ID: 33669380
None
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question