• 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.

Calculation of # of months between end of year and a given inputted date

lfrazier

New Member
I am trying to come up with the formula where by excel can figure the # of months between the end of this current year (12/31/12) and a given inputted date by a customer.


Any suggestions would be greatly appreicated.


Thanks!
 

Luke M

Excel Ninja
If you only care about "whole months" (If user puts Jan 1, 2013 the result is 0 since it hasn't been a whole month) then this should work:

=IF(YEAR(A2)>YEAR(TODAY()),DATEDIF(DATE(YEAR(TODAY()),12,31),A2,"m"),DATEDIF(A2,DATE(YEAR(TODAY()),12,31),"m"))
 

lfrazier

New Member
Luke,


I am only concerned about the whole months.


However, If someone inputs a date that would result in less than 3 months, I want the number to be a minimum of 3 months.


What modifications would need to be made to your current formula?


Thanks,
 

prasaddn

Active Member
Hi Again Ifrazier,


you can try either of the formulas provided above with "if" formula.


i.e. =if(<one_of_the_formula> < 3, 3, <one_of_the_formula>)


thats it.


Regards,

Prasad DN
 

SirJB7

Excel Rōnin
@fred

Hi!

I'd strongly suggest to leave the deads still and well buried.

Give a look at this post and check what recently happened with DATEDIF:

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

There you'll find a simple way to bypassing this function that resembles me to Nearly Headless Nick, a character from Harry Potter: if Redmond guys are going to cut his head off, well, cut it entirely.

Regards!
 

fred

Member
@SirJB7


I read the original question once more and Ifrazier is asking the difference in "months", not "days". So I don't understand what's wrong with my formula. The only thing not clear in his original posting is whether the stated 12/31/2012 is the start or end date. I do not understand what you mean by leaving the deads still and well buried.


I also read the postings in the link you have provided. I think the simple solution should be just cell 2 minus cell 1 and he'll get the answer he's looking for, the number of days between two dates. he doesn't need to use datedif function.
 

SirJB7

Excel Rōnin
@fred


Hi!


As Jack (the ripper) said, let's go by parts.


- I haven't read in detail the whole post, but when I saw that you proposed to use DATEDIF function I believed that you might read the other post linked, where it's been deeply analyzed that function, raised up many errors, and after checking the web I found there were random, unresolved and hiding-intended issues related to it.


- I fully agree with your approach of cell 2 minus cell 1 for this case, as he's expecting days. I don't agree for months or other periods, for example (I just don't because I don't know how to express the subtraction in other units; if you do, please let me know).


- Your formula might be right for a parameter "M", I don't know and I'm not going to verify it, I assume you yed did it, and I don't know any issue for that parameter as I do for "md" (see the related post from Nikita.Kaul)


- Letting the deads well buried is an expresion used to mean that when something's over it may remains in that status unless necessary or convenient. And if Microsoft intentionally omitted documentation references to this function (note the "lost" word in chandoo's post) from Excel 2000 in advance, and if we consider the known issues that it has in certain cases, ... well, I still insist on leaving it six-feet under as I don't find the need or the convenience. Just for safety and accurateness. If someone uses a formula for extracting days, "D", for example, maybe tomorrow he may changes to months, "M", and everything ok, ... 'til he decides to use "md", and then who knows!


Regards!
 

fred

Member
Hi SirJB7


I checked out the file and I guess the user is asking for "only" the days as if they are in complete calendar days between the dates as if they were of the same month and same year.


Then i have a question with your answr on 30 (line 426) if start date is 31st and end date has a 30th. both represent end of the month, right (29 in leap year vs 30 or 31, etc)? then the answer should be a zero? I don't understand why it should be 30 then.


example: 12/31/1999 as start and 4/30/2010


I'm also a bit confused by "md" where dates are considered same month same year. in this case, shouldn't the difference be 1 as in ABS(the difference in dates)???
 

SirJB7

Excel Rōnin
@fred


Hi!


DATEDIF begins counting from the day of the starting date as zero, goes on until it reaches target day. If target day is lesser, it reaches end of month, goes on with day 1 until same condition. That's why from 31 december any-year to 30 april any-year, there are 30 days (from 31, the last one of the month, 1, 2, ... 'til 30, the target).


http://www.cpearson.com/excel/datedif.aspx

In this link it explains what does (or should do) DATEDIF with each Interval. For "md", it exclude days and months.


Regards!
 

fred

Member
Thx, SirJB7.


would be hard to understand this. but thank god I don't have to use it on a daily basis. It is giving me headache to comprehend its rationale.
 
Top