Solved

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

Posted on 2011-02-14
34
1,229 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 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

14 Experts available now in Live!

Get 1:1 Help Now