How to find the exact difference in months between two dates in Access

I have two sets of dates I am trying to find out the exact number of months between them. I have tried two different formulas with different results for each formula. I can't seem to get my head around the year part. I am currently using these formulas in a unbound text box to calculate when a form is opened.

Current Formula is
=IIf(DatePart("d",[CurrentISPDate])>DatePart("d",[PreviousISP]),DateDiff("m",[PreviousISP],[CurrentISPDate])+1,DateDiff("m",[PreviousISP],[CurrentISPDate])-1)

Old Formula is
=DateDiff("m",[PreviousISP],[CurrentISPDate])

With the following two dates here are the results:

Current Formula
3/10/2010 - 9/2/10 - 5 Months

06/25/10 - 1/12/11 - 6 Months

Old Formula
3/10/2010 - 9/2/10 - 6 Months

06/25/10 - 1/12/11 - 7 Months

The current formula works for the exact months as long as the dates are within the same year. Different years the current formula defaults to just months.

What am I missing that will accurately calculate the difference in months between the dates? I know it is something simple.

broken across lines:
monthDiff: 12*(Year([CurrentISPDate])-Year([PreviousISP]))+
(Month([CurrentISPDate])-Month([d1])
+(Day([CurrentISPDate]<Day([PreviousISP]))

years difference * 12 +
months difference +
-1 if the later date is not far along enough in the month OR 0 if it is

Hope this helps,

pT72

0

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

@HainKurt - the second formula you posted works on the dates 6/25/10 and 1/12/11 however if you use the dates 3/10/10 and 9/2/10 the formula tells me 6 months

@GrayL - the formula returns 6 months for 6/25/10 and 1/12/11

You have to use DateAdd as in this function which you should use in your query:

Public Function Months( _ ByVal datDate1 As Date, _ ByVal datDate2 As Date, _ Optional ByVal booLinear As Boolean) _ As Integer' Returns the difference in full months between datDate1 and datDate2.'' Calculates correctly for:' negative differences' leap years' dates of 29. February' date/time values with embedded time values' negative date/time values (prior to 1899-12-29)'' Optionally returns negative counts rounded down to provide a' linear sequence of month counts.' For a given datDate1, if datDate2 is decreased stepwise one month from' returning a positive count to returning a negative count, one or two' occurrences of count zero will be returned.' If booLinear is False, the sequence will be:' 3, 2, 1, 0, 0, -1, -2' If booLinear is True, the sequence will be:' 3, 2, 1, 0, -1, -2, -3'' If booLinear is False, reversing datDate1 and datDate2 will return' results of same absolute Value, only the sign will change.' This behaviour mimics that of Fix().' If booLinear is True, reversing datDate1 and datDate2 will return' results where the negative count is offset by -1.' This behaviour mimics that of Int().' DateAdd() is used for check for month end of February as it correctly' returns Feb. 28. when adding a count of months to dates of Feb. 29.' when the resulting year is a common year.'' 2010-03-30. Cactus Data ApS, CPH. Dim intDiff As Integer Dim intSign As Integer Dim intMonths As Integer ' Find difference in calendar months. intMonths = DateDiff("m", datDate1, datDate2) ' For positive resp. negative intervals, check if the second date ' falls before, on, or after the crossing date for a 1 month period ' while at the same time correcting for February 29. of leap years. If DateDiff("d", datDate1, datDate2) > 0 Then intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2)) intDiff = Abs(intSign < 0) Else intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1)) If intSign <> 0 Then ' Offset negative count of months to continuous sequence if requested. intDiff = Abs(booLinear) End If intDiff = intDiff - Abs(intSign < 0) End If ' Return count of months as count of full 1 month periods. Months = intMonths - intDiffEnd Function

You've done something wrong. It its most basic form my solution 'o' being old and 'n' being new

o=#25 jun 2010#
n=#12 dec 2011#
? o,n
2010-06-25 2011-12-12
? datediff("m",o,n)+(Day(o)-day(n)>0)
17

0

tamckeeAuthor Commented:

I have created a test table and entered some test dates to see the outcomes on the formulas suggested. GrayL - I copied and pasted your formula exactly as typed and but still come up with the same answers.

From that table I generated a report that shows the results of the test along with the solutions suggested. rptTest.pdf I still don't understand why on the 6/25/10 and 1/12/11 dates that all the solutions show 6 months.

as of 01/12/11, month seven has not yet occured and won't until 01/25/11. This is why early on I asked what your definition of a month was.

JimD.

0

tamckeeAuthor Commented:

@JimD - Maybe I should have been more clear. My definition of a month for this purpose is if it one day past six months from the previous date. For example, 6/25/10 to 12/26/10 - this would be considered seven months because it is one day past.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

<<@JimD - Maybe I should have been more clear. My definition of a month for this purpose is if it one day past six months from the previous date. For example, 6/25/10 to 12/26/10 - this would be considered seven months because it is one day past. >>

So up to 07/25/10 is 0 months given 6/25/10 as a start date?

> For example, 6/25/10 to 12/26/10 -
> this would be considered seven months because it is one day past.

That's a weird definition. The six months should be reached at 12/25/10.
If not, three months would be reached 9/26/10 and the next three months at 12/27/10.
Also DateAdd("m", 6, #6/25/10#) returns 12/25/10.

This is how my Months function works. And it performs correct also for the tricky dates around 28th-29th Feb. and 1th Mar.

/gustav

0

tamckeeAuthor Commented:

@cactus_data, I agree with you it is a weird definintion but because of some stipulations in our business if we document items as described we could face penatlies. In example, 6/25/10 to 12/26/10 that would be considered overdue because the CurrentDate is one day past six months.

> In example, 6/25/10 to 12/26/10 that would be considered overdue
> because the CurrentDate is one day past six months.

Then we agree but that was not what you wrote before - then the six months was reached at 12/26/10.
If overdue at 12/26/10, the six month is reached the day before: 12/25/10.
Thus, my function fits your need. And it won't fail for leap years.

/gustav

0

tamckeeAuthor Commented:

First, let me apologize for not being clear on my definition of a month. When I read JDettman's post and saw " ie. 01/10/11 vs 02/09/11 is 0 months and 01/10/11 vs 02/10/11 is 1?" I mistakenly assumed that would be clear. Since in his example 1/10/11 to 2/10/11 equals a month and if you take that further 6/25/10 to 12/25/10 would be six months. This is a lesson learned for me.

Now to the solution.... @catcus_data when I run your function in my form, your results still show 6 months for 6/15/10 to 1/12/11.

@grayl - your solution after I explained works and gives me the answer I am looking for.

So, at 2011-01-12 you haven't reached seven months, sorry.

/gustav

0

tamckeeAuthor Commented:

Yes cactus_data, I understand your function but that is not the results that I need. Based on the explanation of months I finally gave, I needed the function to show 7 months on or after 12/26/10. While I completely understand your function for my current definition of a month for this application, the fucntion has to resolve to 7 months one day after in 6/25/10 to 12/25/10.

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

I would write it as a function, but what is your definition of a "month".

ie. 01/10/11 vs 02/09/11 is 0 months and 01/10/11 vs 02/10/11 is 1?

Jim.