• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count no. of month

S

shibulal

Member
I Need to count the exact no. of months in between 2 particular dates.

How could it be extracted ? Please help.
 
Hi shibulal,


Tried this??
Code: 
=(EndDate - StartDate)/30


..or you are looking for something else!


Regards,

Faseeh
 
Hi, shibulal!


I'd suggest you to not use DATEDIF function with the parameter "M". It has bugs that are not fixed even in 2010 version. Being a useful function it has been so poorly implemented that the last time Microsoft included it in the official documentation was in Excel 2000; after that it disappeared from the scene. The unrecognized bug is the answer.


This issue has been widely analyzed here, give a look at the first link which leads you to the second link where it's discussed and proved working wrong. You'll find there a simple two-pass workaround.


http://chandoo.org/forums/topic/calculation-of-of-months-between-end-of-year-and-a-given-inputted-date#post-17696

http://chandoo.org/forums/topic/excel-2007


Regards!
 
If you don't care about a whole month difference, perhaps this?

=TEXT(EndDate,"yyyymm")-TEXT(StartDate,"yyyymm")


If you do care, I think this will work:

=INT((TEXT(EndDate,"yyyymmdd")-TEXT(StartDate,"yyyymmdd"))/100)
 
@Montrey

Hi!

I don't agree. Have you read the issues concerning DATEDIF with "M" parameter?

Regards!
 
@Montrey

Hi, PEEG!

Those specified and with detailed examples in the two links (specially the second one) of my first post above.

Regards!

PS: Otherwise why should I have posted the two links? Even more with this advise (quoted) "This issue has been widely analyzed here, give a look at the first link which leads you to the second link where it's discussed and proved working wrong."
 
If you really want to dig into the nitty gritty, check this post out:

http://www.mrexcel.com/forum/showpost.php?p=1587099&postcount=20


As my formula isn't an attempt to duplicate the functionality of DateDif, but only calculate months passed, I think it will work for this case. Would be open to feed back from our fellow contributors.
 
@Montrey

My thoughts exactly. There are people out there who are waaaaay more into math and stuff than I am.

And I am okay with that. =P
 
@Montrey


Hi!


I wouldn't dare to affirm such a thing. If you haven't read all the post and its links (which I suppose you haven't unless you read really quickly), give a look at this other link (included in previous) where it isn't possible to explicitly detail under what conditions it fails: DATEDIF simply fails. To check where, you have to add a helper column to control it. I'd never suggest to do such a thing under any circumstance.

http://blog.contextures.com/archives/2010/02/01/calculating-ages-in-excel/


That's why it's "treated as the drunk cousin of the Formula family": every family has one but nobody talks about him.


If you haven't neither read it, here's the workaround I found:

=DAY(B1)-DAY(A1)+IF(DAY(A1)>DAY(B1),DAY(EOMONTH(A1,0)),0)

It's referenced in this post, within the same topic:

http://chandoo.org/forums/topic/excel-2007#post-16879


Regards!
 
I'll admit, I still do. But only for my personal use, where I can trust that I'll have the sense to do a double check if the answer seems screwy, but not in a public workbook.
 
You must log in or register to reply here.
Back
Top