CODEWITHSUNDEEP
exceldatevba
rvictordelta
rvictordelta

Reputation: 668

Finding the week number of the quarter in Excel VBA

I need a VBA function that will return the week number in a quarter from a given date.

For example, Input of 1/1/2016 will return 1, 1/4/2016 will return 2, 10/1/2016 will return 1, 10/7/2016 will return 2, 11/11/2016 will return 7

I have a function in Excel that does this:

=IF(O48="","",WEEKNUM(O48)-WEEKNUM(LOOKUP(O48,DATE(YEAR(O48),{1,4,7,10},1)))+1)

But I am struggling to port it to VBA. Help? Thanks!

Upvotes: 0

Views: 867

Answers (1)

Comintern
Comintern

Reputation: 22195

You can use DatePart to get the week number in the year, then subtract 13 weeks for each quarter (also available with the DatePart function):

Public Function WeekOfQuarter(inValue As Date)
    WeekOfQuarter = DatePart("ww", inValue) - ((DatePart("q", inValue) - 1) * 13)
End Function

Upvotes: 1

Related Questions