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. :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.