Full Version: Difference in Months between two Dates
Mike15739
Hey guys,

Question for you. What is the best way to find the difference in Months between Two Dates. I have tried many formulas and I haven't found one that works the best. The total number of months are off by 1 for dates that are at the end of the month (ex. 03/29/07, 03/30/07). Here is one of the equations that I have found that work the best so far.

=IF((\$L\$2-J2)>1,ROUNDUP(((\$L\$2-J2)/30),0),0) where L2 is the most recent date (Cut-Off Date) and J2 is the Date they had until considered late. I work for a bank so that is why I am trying to figure this out.

The old way of doing it was just listing on a piece of a paper, 1-100 and from there, you put last month's date as number one and go backwards from there. They are dead set on saying that is the best way to calculate the number of months but I am not seeing it. Every place online has said the best way to calculate difference between dates is to take the most recent one minus the older date and divide that by 30.

Any other suggestions would be much appreciated.

Thanks,

Mike
Try the following:

=Sum(Month(CellNumber)-Month(CellNumber))

ex. =Sum(Month(B2)-Month(A2))
Mike15739
Sorry man, it doesn't work. It just shows you negative numbers

ex.
3/31/2007 - 9/23/2005 = -6 where it should be 19

Thanks though,

Mike
dashiellx2000
Use the DateDif Function

DateDif( CellNumber, CellNumber, M )

HTH.
Mike15739
Thanks man,

I appreciate the help, I will try it out. But you wouldn't happen to know of a formula or program that has the ability to just count months, forgetting days as a whole. So the difference between January and March is 3 months, completely forgetting the days. This is pretty much what they want to detect how many months someone is late on their payments
Mike15739
Oh yeah, just wanted to let you know, I have used that formula, sometimes they were off by 1 if the end of the month and the beginning of the month is their maturity date (cut off day until they are considered late)
Snapper316
This Post Help?

Are you saying January 15th and March 16th is technically 2 months 1 day but you want it to show 3 months?
Mike15739
Yes, exactly, because getting the total number of days and dividing by 30 can make the number be different based on days having on 30 days, or February having 28. I have been scouring the internet for a while now but nothing that would help me. Any suggestions?
Mike15739
I looked at that example you sent me, I am getting #NAME? using the equation =DATEDIFF("m",J2,\$L\$2) where J2 is the date when they would be considered to be late and \$L\$2 is a set date for the end of the reporting month (03/31/2007)
it is is DATEDIF not with two Fs
Mike15739
OKay thanks but now I am getting a Value error, and on the Excel Help section, they said the "m" should be at the end
DateDif( CellNumber, CellNumber, "M" )
Mike15739
Hey RAZ,

Thanks for your help, I have tried that formula before but it was always off by 1 with the way they wanted it using that formula, so I just added 1 to the formula after and it worked, matched all the months they wanted.

Thanks again