Solved

excel difference between two dates

Posted on 2010-08-28
7
429 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel formula 6 23
Importing csv to excel - text formating 14 23
Macro 3 21
Index/Match with Multiple Criteria 2 16
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

746 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now