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.
Current Formula is
=IIf(DatePart("d",[Current
Old Formula is
=DateDiff("m",[PreviousISP
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.
whats the issue with old formula
SELECT DateDiff("m",Now()3483,Now())
I get 114 for above query... now and 3483 days ago...
SELECT DateDiff("m",Now()3483,No
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
monthDiff: 12*(Year([CurrentISPDate])
(Month([CurrentISPDate])M
+(Day([CurrentISPDate]<Day
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
)
DateDiff("m",[PreviousISP]
 iif(
DatePart("d",[CurrentISPDa
)
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.
@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 112?
 iif(
DatePart("d",[CurrentISPDa
)
whats wrong with this? what values did you use? is this always give you 112?
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
)
DateDiff("m",[PreviousISP]
 iif(
DatePart("d",[CurrentISPDa
)
How about:
DateDiff("m",PreviousISP,CurrentISPDate) + (Day(PreviousISP)Day(CurrentISPDate)>0)
DateDiff("m",PreviousISP,C
You don't need the Iif()  it slows things down
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
@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
DateDiff("m",
dateadd("d",datepart("d",
dateadd("d",datepart("d",
)

iif (
DatePart("d",[CurrentISPDa
)
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.
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 18991229)
'
' 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.
'
' 20100330. 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
/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
20100625 20111212
? datediff("m",o,n)+(Day(o)day(n)>0)
17
o=#25 jun 2010#
n=#12 dec 2011#
? o,n
20100625 20111212
? datediff("m",o,n)+(Day(o)
17
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.
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
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<@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.
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=#20100625#
n=#2010725#
? datediff("m",o,n)(Day(n)Day(o)>0)
1
o=#20100625#
n=#2010725#
? datediff("m",o,n)(Day(n)
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 28th29th Feb. and 1th Mar.
/gustav
> 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 28th29th Feb. and 1th Mar.
/gustav
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?
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
> 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
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.
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#)
20101215
? DateAdd("m", 7, #6/15/10#)
20110115
So, at 20110112 you haven't reached seven months, sorry.
/gustav
> @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#)
20101215
? DateAdd("m", 7, #6/15/10#)
20110115
So, at 20110112 you haven't reached seven months, sorry.
/gustav
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
That was not very clear.
/gustav
/gustav: He explained it in http:#a34919819
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.