Link to home
Start Free TrialLog in
Avatar of tamckee
tamckee

asked on

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.
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image


 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.  
whats the issue with old formula

SELECT DateDiff("m",Now()-3483,Now())

I get 114 for above query... now and 3483 days ago...

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
try this:

DateDiff("m",[PreviousISP],[CurrentISPDate])
- iif(
DatePart("d",[CurrentISPDate])>DatePart("d",[PreviousISP]), 0, 1
)
Avatar of tamckee
tamckee

ASKER

@Jim - Yes that is my definition of a month

@pteranodon72 - I tried this formula but when it calculates I get results like 36 months

@HainKurt - With your formula I get the same results as my current formula.
DateDiff("m",[PreviousISP],[CurrentISPDate])
- iif(
DatePart("d",[CurrentISPDate])>DatePart("d",[PreviousISP]), 0, 1
)

whats wrong with this? what values did you use? is this always give you 1-12?
Avatar of tamckee

ASKER

HainKurt, the formula returns the same value as my current formula.   If I use the dates 6/25/10 and 1/12/11 and use your formula, it returns 6 Months.  
oops, try this :)

DateDiff("m",[PreviousISP],[CurrentISPDate])
- iif(
DatePart("d",[CurrentISPDate])>DatePart("d",[PreviousISP]), 1, 0
)
How about:

DateDiff("m",PreviousISP,CurrentISPDate) + (Day(PreviousISP)-Day(CurrentISPDate)>0)


You don't need the Iif()  -  it slows things down
Avatar of tamckee

ASKER

@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
ok, try this:

DateDiff("m",
dateadd("d",-datepart("d",[PreviousISP])+1,[PreviousISP]),
dateadd("d",-datepart("d",[CurrentISPDate])+1,[CurrentISPDate])
)
-
iif (
DatePart("d",[CurrentISPDate])>DatePart("d",[PreviousISP]), 0, 1
)

idea is, we get the month diff on the first day of month of given dates
then check the days of dates
Here's a function that might be a bit clearer to understand:

Function DiffInMonths(dtStartDate As Date, dtEndDate As Date) As Long

    If dtStartDate > dtEndDate Then
        MsgBox "Invalid call - start > end date"
        DiffInMonths = 0
    Else
        DiffInMonths = DateDiff("m", dtStartDate, dtEndDate)
        If DatePart("d", dtEndDate) < DatePart("d", dtStartDate) Then DiffInMonths = DiffInMonths - 1
    End If
         
End Function

JimD.
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 - intDiff
  
End Function

Open in new window

/gustav
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


Avatar of tamckee

ASKER

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.

Well, it is a 201 days' interval.
How could that possibly be less than 6 full months? It includes the full second half year of 2010.

If you wish the result to be 7, you probably should go with DateDiff:

? DateDiff("m",#6/25/10#, #1/12/11#)
 7

/gustav
<<  I still don't understand why on the 6/25/10 and 1/12/11 dates that all the solutions show 6 months.>>

1  07/25/10
2  08/25/10
3  09/25/10
4  10/25/10
5  11/25/10
6  12/25/10

  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.
Avatar of tamckee

ASKER

@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.  
Now you've finally told us.  
ASKER CERTIFIED SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<<@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?

JimD.
my solution shows that to be 1 month:

o=#2010-06-25#
n=#2010-7-25#
? datediff("m",o,n)-(Day(n)-Day(o)>0)
 1
> 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
Avatar of tamckee

ASKER

@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.
tamckee:  does my post at http:#a34920138 give you what you are looking for?
Avatar of tamckee

ASKER

@grayl, I will be trying it in a few minutes.  
> 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
Avatar of tamckee

ASKER

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.



Thanks, glad to help
Some confusion exists.

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

Of course, because it _is_ 6 months:

? DateAdd("m", 6, #6/15/10#)
2010-12-15
? DateAdd("m", 7, #6/15/10#)
2011-01-15

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

/gustav
Avatar of tamckee

ASKER

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.  
So you are rounding up I guess.
That was not very clear.

/gustav
/gustav:  He explained it in http:#a34919819