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))

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

Use the DateDif Function

DateDif( CellNumber, CellNumber, M )



HTH.

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

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)

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?

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?

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

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" )

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

Great!!!! I'm glad I could help and in the process I learned something too.

RAZ

sorry mine is in Access

Oh, don't worry about it Snap, your post got me off to the right start again, I appreciate it. Have a good one guys