?
Solved

excel difference between two dates

Posted on 2010-08-28
7
Medium Priority
?
443 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 400 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 300 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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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 300 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

601 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