Datediff, number of months total

[Greyowlsl]

Hi,

I need the number of months in total between two dates, such as 07-2010 with 09-2011 = 14 rather than 2

How would i code this

DateDiff("m", date1, date2)

Thanks,
Leon

 

[John Big Booty]

Should do the trick, check this here for the various variables available for the DateDiff() function.

 

[John Big Booty]

... you may need to use the DateSerial() function to form those dates into a fully formed dates rather than simply a month and year.

 

[Greyowlsl]

Hi John,

Not quite sure what you mean with the dateserial, these arn't fixed dates. The only thing i can think to solve this is:

Dim D_Y as Integer
Dim D_M as Integer
D_Y = ((Datediff ("yyyy", date1, date2)) - 1) * 12
D_M = (datediff ("m", date1, date2)) + D_Y

Just thought there may be some small function for this.

Thanks,
Leon

 

[John Big Booty]

Currently 07-2010 is not a fully formed date, it needs a day (number) of the month. Given that you are only interested in the month difference, that day number could be the first of the month. So you would use the DateSerial() function to form, say, 07-2010 into the fully formed date 1/07/2010 which can then be manipulated by the DateDiff() function.

 

[Greyowlsl]

Oh, sorry i should have explained better, those dates were just examples. Date1 and Date2 are full dates, but are over a year apart. Its just the the "m" variable only looks at the month difference within a 1 year not all the years, so for example: Datediff ("m", 1/01/2006, 1/05/2011) gives the value 4, when i want the value 64 (no. of total months between the two dates).

 

[pbaldy]

Note the # required for a literal date:

?datediff("m",#1/01/2006#,#1/05/2011#)
60

Difference due to the US date format.

 

[Greyowlsl]

Note the # required for a literal date:

?datediff("m",#1/01/2006#,#1/05/2011#)
60

Difference due to the US date format.

Hi pbaldy,

That does work, however my datediff line looks like this:

W_C2 = DateDiff("m", (DateAdd("yyyy", W_C, Me.[WARRANTY RETURNED])), [Date recieved])

(and yes i know the received is spelt wrong)
I can't add # in there; At the moment the datediff looks at the date in AUS date format, but when using # it uses US format, so even if i put # into the code how would i make it looks at it in AUS date format?

Thanks,
Leon

 

[pbaldy]

Actually you want it in US format:

http://allenbrowne.com/ser-36.html

 

[missinglinq]

As Paul said and Allen explains in his article, you need to use the US date format for this kind of thing.

You also need to keep in mind that when using "m" in DateDiff() Access only looks at the month and year parts of the date, so that

DateDiff("m",#8/31/2011#,#9/1/2011#)

will return 1 month while, in fact, the dates are only 1 day apart! This may or may not adversely effect the results you're looking for.

Linq ;0)>

 

[Greyowlsl]

Thanks everyone for your time and help.

I haven't used access in a long while, so it took me a while to get my head around this code. I had realised in the end i didn't need to have a month total
I just needed to code it differently... I know alot more about date literals and interpretations though .

I put the code i used below (works completely ).

Thanks again,
Leon

Private Sub Form_Load()
Dim W_C As Integer
Dim W_C2 As Integer
Dim D_Y As Integer
Dim D_A As Date

If (Me.[PRODUCT ID] = "PVE1200") Or (Me.[PRODUCT ID] = "PVE2500") Then
Else: GoTo NO_PVE
End If

If (IsNull(Me.[WARRANTY RETURNED])) Or (Me.[WARRANTY RETURNED] = "") Then
GoTo NO_CARD
End If

''Warranty extention check
If Me.[WARRANTY RETURNED] <= #1/1/2009# Then
    W_C = 3
    Else
    W_C = 5
End If

D_A = DateAdd("yyyy", W_C, Me.[WARRANTY RETURNED])
D_Y = DateDiff("yyyy", D_A, [Date recieved])

If D_Y >= 1 Then
GoTo NO_W
ElseIf D_Y = 0 Then
GoTo MONTH_C
Else: GoTo YES_W
End If

MONTH_C:
'' Month check with 1 month date leniency
W_C2 = DateDiff("m", D_A, [Date recieved])
If (W_C2 >= 0) Or (W_C2 = -1) Then
GoTo NO_W
Else: GoTo YES_W
End If

NO_PVE:
Me.warrantycontrol = "Non PVE"
Exit Sub

NO_CARD:
Me.warrantycontrol = "No card"
Exit Sub

NO_W:
Me.warrantycontrol = "No"
Exit Sub

YES_W:
Me.warrantycontrol = "Yes"

End Sub