May 7 2007, 08:02 AM
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.
May 7 2007, 08:19 AM
Try the following:
May 7 2007, 08:27 AM
Sorry man, it doesn't work. It just shows you negative numbers
3/31/2007 - 9/23/2005 = -6 where it should be 19
May 7 2007, 08:40 AM
Use the DateDif Function
DateDif( CellNumber, CellNumber, M )
May 7 2007, 08:44 AM
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
May 7 2007, 08:46 AM
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)
May 7 2007, 10:09 AM
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?
May 7 2007, 11:04 AM
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?
May 7 2007, 11:13 AM
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)
May 7 2007, 11:33 AM
it is is DATEDIF not with two Fs
May 7 2007, 11:43 AM
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
May 7 2007, 01:43 PM
DateDif( CellNumber, CellNumber, "M" )
May 7 2007, 02:08 PM
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.
May 7 2007, 02:11 PM
Great!!!! I'm glad I could help and in the process I learned something too.
May 7 2007, 02:20 PM
sorry mine is in Access
May 7 2007, 02:21 PM
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here