Solved

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

Posted on 2011-02-14
34
1,215 Views
Last Modified: 2012-05-11
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
Comment
Question by:tamckee
  • 9
  • 9
  • 6
  • +3
34 Comments
 
LVL 57
ID: 34891530

 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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34891607
whats the issue with old formula

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

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

0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 34891640
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34891767
try this:

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

Author Comment

by:tamckee
ID: 34896733
@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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34897499
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
 

Author Comment

by:tamckee
ID: 34897635
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34897685
oops, try this :)

DateDiff("m",[PreviousISP],[CurrentISPDate])
- iif(
DatePart("d",[CurrentISPDate])>DatePart("d",[PreviousISP]), 1, 0
)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34897986
How about:

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


0
 
LVL 44

Expert Comment

by:GRayL
ID: 34898003
You don't need the Iif()  -  it slows things down
0
 

Author Comment

by:tamckee
ID: 34898985
@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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34899928
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
 
LVL 57
ID: 34900131
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34901536
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
 
LVL 44

Expert Comment

by:GRayL
ID: 34903075
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
 

Author Comment

by:tamckee
ID: 34918786
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34919007
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 57
ID: 34919131
<<  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
 

Author Comment

by:tamckee
ID: 34919819
@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
 
LVL 44

Expert Comment

by:GRayL
ID: 34920002
Now you've finally told us.  
0
 
LVL 44

Accepted Solution

by:
GRayL earned 250 total points
ID: 34920138
Try this:

o=#2010-06-25#
n=#2010-12-26#
? datediff("m",o,n)-(Day(n)-Day(o)>0)
 7
0
 
LVL 57
ID: 34920163
<<@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
 
LVL 44

Expert Comment

by:GRayL
ID: 34920217
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34920777
> 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
 

Author Comment

by:tamckee
ID: 34920918
@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
 
LVL 44

Expert Comment

by:GRayL
ID: 34920996
tamckee:  does my post at http:#a34920138 give you what you are looking for?
0
 

Author Comment

by:tamckee
ID: 34921083
@grayl, I will be trying it in a few minutes.  
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34921210
> 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
 

Author Comment

by:tamckee
ID: 34945337
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
 
LVL 44

Expert Comment

by:GRayL
ID: 34945503
Thanks, glad to help
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34945783
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
 

Author Comment

by:tamckee
ID: 34945865
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34946008
So you are rounding up I guess.
That was not very clear.

/gustav
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34946087
/gustav:  He explained it in http:#a34919819
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now