Get the number of the week of the month from a given date

Question

I have a set of dates from which I must obtain the week of the month.

There is a lot of literature on how to obtain the week of the year using VBA code but not the week number of the month.

For instance 03-Mar-13 would give week 1 of March, instead I end up with a result of week 10.

Marci

Answer 1

This isn't the most elegant code, but it worked for me.

Some assumptions:

1. This is a UDF and can be used on a spreadsheet or in code
2. Weeks start on Sundays
3. Week 1 can be incomplete week

=====

Function WeekOfMonth(selDate As Date)
    Dim DayOfFirst As Integer
    Dim StartOfWeek2 As Integer
    Dim weekNum As Integer

    DayOfFirst = Weekday(DateSerial(Year(selDate), Month(selDate), 1), vbSunday)
    StartOfWeek2 = (7 - DayOfFirst) + 2

    Select Case selDate
        Case DateSerial(Year(selDate), Month(selDate), 1) _
        To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 - 1)
            weekNum = 1

        Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2) _
        To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 6)
            weekNum = 2

        Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 7) _
        To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 13)
            weekNum = 3

        Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 14) _
        To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 20)
            weekNum = 4

        Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 21) _
        To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 27)
            weekNum = 5

        Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 28) _
        To DateSerial(Year(selDate), Month(selDate) + 1, 1)
            weekNum = 6
    End Select

    WeekOfMonth = weekNum
End Function

Cade

Awsome, an up vote for this code. I have even implemented it in an SQL Query and on a Filter in a Form. Works perfectly. See Query: SELECT WeekOfMonth([table1]![date_field]) AS WeekNumber FROM table1; – Chant

Answer 2

I know this is old, but I came across this and thought I would add my code. (Built from rvalerio's answer)

Private Function getWeekOfMonth(testDate As Date) As Integer
    getWeekOfMonth = CInt(Format(testDate, "ww")) - CInt(Format(Format(testDate, "mm/01/yyyy"), "ww")) + 1
End Function

Nicole

Answer 3

There are a few clever answers here, however the question did say, "For instance 03-Mar-13 would give week 1 of march." The answers provided here will return week 2 for that date because March 1st is day 6 of a week, which makes March 3rd day 1 again, and thus part of week 2.

To put it another way, the methods given by guitarthrower, rvalerio, and user3496574 are equivalent to counting the rows on a monthly calendar, as opposed to counting the full weeks starting at day one. So the results can go up to 6, whereas if you are counting by the full week, you can go up only to 5 (and February obviously has only 4 full weeks).

It depends on how you want to count the weeks. I don't know that either way is right or wrong. But technically if you want to count weeks as blocks of 7 days that begin on the 1st of the month -- which is what I wanted to do -- then you need to get the day of the month, divide by 7, and then round up.

So in Excel it is something like:

=ROUNDUP(DAY(MyDate)/7,0)

VBA doesn't have a round up function built-in, but obviously this would work:

Function WeekOfMonth(TestDate As Date) As Integer
    WeekOfMonth = Application.WorksheetFunction.RoundUp(Day(TestDate) / 7, 0)
End Function 

To be as complete as possible, here is a solution that is slightly longer but doesn't access an Excel function, and since it uses fairly simple logic it might be more efficient if more data is involved:

Function WeekOfMonth(TestDate As Date) As Integer

    WeekOfMonth = RoundUpVBA(Day(TestDate) / 7,0)

End Function


Function RoundUpVBA(InputDbl As Double, Digits As Integer) As Double

    If InputDbl >= O Then
        If InputDbl = Round(InputDbl, Digits) Then RoundUpVBA = InputDbl Else RoundUpVBA = Round(InputDbl + 0.5 / (10 ^ Digits), Digits)
    Else
        If InputDbl = Round(InputDbl, Digits) Then RoundUpVBA = InputDbl Else RoundUpVBA = Round(InputDbl - 0.5 / (10 ^ Digits), Digits)
    End If

End Function

Also, here is the Excel formula for user3496574's answer, which calculates the week number the other way:

=WEEKNUM(MyDate) - WEEKNUM(EOMONTH(MyDate,-1)+1)+1

Which could well be faster in some cases than the VBA version.

The rest of this here is just additional discussion about the algorithms, rounding, and optimization that you can read if you're interested, but the main answer is above.

Stack Overflow provides a discussion of some user-contributed rounding functions in VBA, but the answers are inconsistent. There is also a C++ discussion of rounding up only that is not hard to follow. But I think that the method above is fairly to-the-point.

If you do want to round in VBA, then take care that you're not trying to round up an integer, which is already rounded (and maybe not in the way that you wanted).

Also note that, probably to try to confuse us, VBA's Round function uses Banker's rounding (round-half-even), whereas Excel's rounding does not -- and nor does CInt, which also works differently from Int (and Fix), which truncate the fractional part.

I don't think that using Banker's rounding was a wise engineering decision, but it's what they decided to use. This type of rounding, which can round the .5 part either up or down, reminds me of how cars today try to outsmart us. For instance, if the windshield wipers are on in mine and I shift into reverse then the rear wipers go on, which at first seems like an electrical glitch, rather than a feature. The reason for Banker's rounding is to eliminate the upward bias that accumulates if you try to round a whole bunch of numbers.

Now, you can get the results supplied by the previous answers here by using my method, too, if you just offset the day.

So in Excel:

=ROUNDUP(((DAY(MyDate)+WEEKDAY(EOMONTH(MyDate,-1)+1)-1) / 7), 0)

To figure out the offset, I have to get the weekday for the first day of the month.

So for 03-Mar-13 instead of taking 3 and dividing it by 7, I'm taking 8 and dividing it by 7. So I get 2 now instead of 1.

In VBA:

Function WeekOfMonth(TestDate As Date) As Integer

    Dim FirstDayOfMonth As Date
    Dim Offset As Integer

    FirstDayOfMonth = TestDate - Day(TestDate) + 1
    Offset = Weekday(FirstDayOfMonth)

    WeekOfMonth = Application.WorksheetFunction.RoundUp((((Day(TestDate) + Offset) - 1) / 7), 0)

End Function

Exceljet supplies these two elegant methods for getting the first day of the month.

You can also take the algorithms from the original answers and use an offset to get my results, although it's not worth it. The formula below does a quick offset, but fails at the end of the month, and adding more complication would only make it less useful:

=getWeekOfMonth(MyDate-WEEKDAY(EOMONTH(MyDate,-1)+1)+8)-1

And finally, if you want to do some really quick and dirty rounding up in VBA for these purposes only, here are several hacker-worthy ugly ways to do it. First:

WeekOfMonth = Day(TestDate) / 7  ' divide by 7
WeekOfMonth = Fix(WeekOfMonth - 0.0001) + 1  ' ugly rounding up

The rounding part is only one line of code. It truncates the decimal point part of the number, but first subtracts a little so that days 1 through 7 give the same result, and then it adds a day so that we start at 1 instead of at 0.

An even uglier way:

WeekOfMonth = Day(TestDate) / 7  ' divide by 7
WeekOfMonth = Day(WeekOfMonth + 1.9999)  ' uglier rounding up

That takes advantage of the fact that to VBA a day is just the date value without the decimal point part. (And note that again, to mess with us, VBA and Excel will return different results for DAY in this case.)

As I mentioned, Int will also truncate, but another trick that could be used is that CBool and booleans treat a 0 as false but a fraction as true, and a boolean can then be converted back into a whole number -- so boolean logic could be used to convert a fraction into a whole number. Not that we're code golfing or anything.

Now, for example if I use the first ugly technique, then this is a self-contained function:

Function WeekOfMonth(TestDate As Date) As Integer

    Dim TempWeekDbl As Double

    TempWeekDbl = Day(TestDate) / 7  ' divide by 7
    TempWeekDbl = Fix(TempWeekDbl - 0.0001) + 1  ' ugly rounding up

    WeekOfMonth = TempWeekDbl

End Function

For a function that works for all numbers but only rounds to the whole number, you can use:

Function RoundUpToWhole(InputDbl As Double) As Integer

    Dim TruncatedDbl As Double

    TruncatedDbl = Fix(InputDbl)

    If TruncatedDbl <> InputDbl Then
        If TruncatedDbl >= 0 Then RoundUpToWhole = TruncatedDbl + 1 Else RoundUpToWhole = TruncatedDbl - 1
    Else
        RoundUpToWhole = TruncatedDbl
    End If

End Function

That function and the original RoundUpVBA function I listed above imitate the Excel ROUNDUP function, which uses round-away-from-zero. Excel's ROUND by contrast uses round-half-up.

The quick one-liner rounding methods I mention here are not all that elegant, and work only because we know that the number is positive, and that the smallest fractional part of the number that we can get is about 0.14 (which is much greater than 0.0001). If you're separating out the round-up function to be a general purpose one, then it's better to do it right, to avoid later headaches. But if the algorithm is just contained within this function and is properly marked as ugly rather than as elegant and all-purpose, then it's OK, I believe.

Famous last words.

I wonder if "Famous last words" were ever anyone's famous last words.

Rabaul

Answer 4

You could perhaps calculate it this way:

• calculate week1 = week of the year of 1st of MONTH
• calculate week2 = week of the year of Nth of MONTH
• desired result = week2 - week1 + 1

Does this help?

Etra

Answer 5

I saw this code and tried.. Not working for me but I love the idea

Private Function getWeekOfMonth(testDate As Date) As Integer
getWeekOfMonth = CInt(Format(testDate, "ww")) - CInt(Format(Format(testDate, "mm/01/yyyy"), "ww")) + 1 End Function

And I changed the code

getWeekOfMonth = CInt(Format(testDate, "ww")) - CInt(Format("01."+Format(testDate,"mm.yyyy"), "ww"))

Deploy

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Zeeba