calculate week number of quarter (help needed)

[werafa]

Hi All,

I'm attempting to calculate the week number of the quarter based on a given date.
I've written a VBA function that appears to do this correctly when fed with test dates.

It does not work correctly when called from my query however. (qWeek: WeekOfQuarter([dateto]))

I get:
30/6/2017 (30th June) = week 14 (Should be 13
26/6/17 = week 14 (should be 12)
18/6/17 = week 13 (should be 11)
......
9/4/17 = week 3 (should be 1)

it is:

Public Function WeekOfQuarter(myDate As Date) As Long
'calculate the week of the most recent quarter for the given date
Dim myWeek As Long, myQ As Long
Dim qDate As Date
    
    'get first date of current quarter
    myQ = DatePart("q", myDate)
    Select Case myQ
        Case Is = 1
            qDate = CDate("1/1/" & Year(myDate))
        Case Is = 2
            qDate = CDate("1/4/" & Year(myDate))
        Case Is = 3
            qDate = CDate("1/7/" & Year(myDate))
        Case Is = 4
            qDate = CDate("1/10/" & Year(myDate))
    End Select
    
    ' get difference in weeks
    myWeek = DateDiff("ww", qDate, myDate, vbSunday, vbFirstJan1) + 1
    'Debug.Print myWeek & " " & myDate


WeekOfQuarter = myWeek
End Function

Can anyone spot what I'm doing wrong - or tell me if there is a better way to do this.
Thanks
Werafa

[werafa]

Teach me to trust the data that some creative spreadsheet designer has 'gifted' to me =

the code does work perfectly. you can tweak it by playing with the datediff command, eg, change vbSunday to vbMonday to calculate weeks based on Monday = day 1 of the new week. To use it, create a standard VBA module in the VBA editor, (I rename mine to 'CustomFunctions"), paste in the code, and call it using <fieldName: WeekOfQuarter([yourDate])>

I hope you find this useful, and thank you to everyone who has helped steer me straight so far.
Werafa