获取给定日期的月份中的星期几 [英] Get the number of the week of the month from a given date

问题描述

我有一组日期，必须从这些日期中获取月份中的星期。

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.

例如，03年3月3日将给出3月的第1周，相反，我得到的是周的结果10。

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

推荐答案

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

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

一些假设：

1. 这是UDF，可用于电子表格或代码中


2. 每周从星期日开始


3. 第1周可能是不完整的一周

=====

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

这篇关于获取给定日期的月份中的星期几的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！