PDA

View Full Version : VBA DateDiff() Problem



mpento01
03-20-2007, 02:29 PM
When using DateDiff in vba i'm finding it works inconsistantly

For example

Debug.Print DateDiff("q", "7/17/2007", "6/16/2007")
Debug.Print DateDiff("q", "4/17/2007", "1/16/2007")
Debug.Print DateDiff("q", "11/17/2007", "10/16/2007")
Debug.Print DateDiff("q", "8/17/2007", "7/16/2007")

would give
-1
-1
0
0

Surely whatever the correct result is they should all be the same.




Thank in advance

Ken Puls
03-20-2007, 03:20 PM
Looks fine to me. You're asking for dates from different quarters. ("q")

6/16 is in the first quarter, 7/17 in the second (hence -1)
Jan/April are one quarter removed
Oct/Nov are same quarter, and so are July/Aug

mpento01
03-20-2007, 03:34 PM
Looks fine to me. You're asking for dates from different quarters. ("q")

6/16 is in the first quarter, 7/17 in the second (hence -1)
Jan/April are one quarter removed
Oct/Nov are same quarter, and so are July/Aug


Ok i see. Damn it. That messes up what i wanted to do bigtime. What if i want Feb/May to be one quarter. Is there a way to do this.

So basically Quarters 1, 4, 7, 10 will work perfectly.

However 2, 5, 8, 11
and 3, 6, 9, 12 will not work.

Is there any way to accomodate starting on these quarter months

Thanks Ken

Ken Puls
03-20-2007, 04:05 PM
Someone else may have a better way, but I'd probably write a UDF for that myself, although you could skip it if you're only going to use it once.

Function myqdif(dtStart As Date, dtEnd As Date) As Long
Dim dtEndAdjusted As Date
Dim dtStartAdjusted As Date

dtStartAdjusted = DateAdd("m", 2, dtStart)
dtEndAdjusted = DateAdd("m", 2, dtEnd)

myqdif = DateDiff("q", dtStartAdjusted, dtEndAdjusted)
End Function

Basically, it shifts the dates by 2 months.

Hope thats what you meant.

mpento01
03-20-2007, 04:10 PM
This seem like a decent workaround for anyone who is interested. What do ya think?



'Represent the difference in quarters
Dim qDiff as Integer
Select Case Month(date1)
Case 1, 4, 7, 10

qDiff = DateDiff("q", date1, date2)

Case 2, 5, 8, 11
qDiff = DateDiff("q", DateAdd("m", -1, date1), DateAdd("m", -1, date2))

Case 3, 6, 9, 12
qDiff = DateDiff("q", DateAdd("m", -2, date1), DateAdd("m", -2, date2))

End Select




End Select

mpento01
03-20-2007, 04:13 PM
Someone else may have a better way, but I'd probably write a UDF for that myself, although you could skip it if you're only going to use it once.

Function myqdif(dtStart As Date, dtEnd As Date) As Long
Dim dtEndAdjusted As Date
Dim dtStartAdjusted As Date

dtStartAdjusted = DateAdd("m", 2, dtStart)
dtEndAdjusted = DateAdd("m", 2, dtEnd)

myqdif = DateDiff("q", dtStartAdjusted, dtEndAdjusted)
End Function

Basically, it shifts the dates by 2 months.

Hope thats what you meant.


Very sorry Ken. I posted before i say your reply. Thanks very much.

All the best

EDIT:
I don't think your function will work on the second scenario i.e. q 3,6,9,12 situation just in case anyone is reading this.

This might work better



Function qdiff(date1 As Date, date2 As Date) As Integer

Select Case Month(date1)
Case 1, 4, 7, 10

qDiff = DateDiff("q", date1, date2)

Case 2, 5, 8, 11
qDiff = DateDiff("q", DateAdd("m", -1, date1), DateAdd("m", -1, date2))

Case 3, 6, 9, 12
qDiff = DateDiff("q", DateAdd("m", -2, date1), DateAdd("m", -2, date2))

End Select
End Function

Ken Puls
03-20-2007, 04:27 PM
No need to apologize. It's just different ways to tackle the same issue.

I haven't tested yours, but if it works, great. That's what really matters in the end. :)