Calculation of # of months between end of year and a given inputted date
- Thread starter lfrazier
- Start date
Kyle McGhee
Member
Hi Ifrazier,
I'd suggest that rather than the IF formula, use MAX.
=MAX(formula ,3)
I'd suggest that rather than the IF formula, use MAX.
=MAX(formula ,3)
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!
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!
@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.
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!
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!
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)???
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!
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!