excel - 获取从给定日期开始的月份中的第几周

Question

我有一组日期，我必须从中获取该月的第几周。

有很多文献介绍如何使用 VBA 代码获取一年中的第几周，但没有介绍该月的周数。

例如，2013 年 3 月 3 日将给出 3 月的第 1 周，但我最终得到的结果是第 10 周。

Answer 1

这不是最优雅的代码，但它对我有用。

一些假设:

1. 这是一个 UDF，可以在电子表格或代码中使用
2. 每周从周日开始
3. 第一周可能是不完整的一周

=====

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