• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1285
  • Last Modified:

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.
0
tamckee
Asked:
tamckee
  • 9
  • 9
  • 6
  • +3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 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.  
0
 
HainKurtSr. System AnalystCommented:
whats the issue with old formula

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

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

0
 
pteranodon72Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HainKurtSr. System AnalystCommented:
try this:

DateDiff("m",[PreviousISP],[CurrentISPDate])
- iif(
DatePart("d",[CurrentISPDate])>DatePart("d",[PreviousISP]), 0, 1
)
0
 
tamckeeAuthor Commented:
@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.
0
 
HainKurtSr. System AnalystCommented:
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?
0
 
tamckeeAuthor Commented:
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.  
0
 
HainKurtSr. System AnalystCommented:
oops, try this :)

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

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


0
 
GRayLCommented:
You don't need the Iif()  -  it slows things down
0
 
tamckeeAuthor Commented:
@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
0
 
HainKurtSr. System AnalystCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
Gustav BrockCIOCommented:
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
0
 
GRayLCommented:
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.

0
 
Gustav BrockCIOCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<  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.
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.  
0
 
GRayLCommented:
Now you've finally told us.  
0
 
GRayLCommented:
Try this:

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

JimD.
0
 
GRayLCommented:
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
0
 
Gustav BrockCIOCommented:
> 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.
0
 
GRayLCommented:
tamckee:  does my post at http:#a34920138 give you what you are looking for?
0
 
tamckeeAuthor Commented:
@grayl, I will be trying it in a few minutes.  
0
 
Gustav BrockCIOCommented:
> 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.



0
 
GRayLCommented:
Thanks, glad to help
0
 
Gustav BrockCIOCommented:
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
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.  
0
 
Gustav BrockCIOCommented:
So you are rounding up I guess.
That was not very clear.

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 9
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now