vba 从给定日期获取一个月中的第几周

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

提问by user2453446

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.

有很多关于如何使用 VBA 代码获取一年中的第几周而不是一个月的周数的文献。

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

例如 03-Mar-13 将给出 3 月的第 1 周，而不是我最终得到第 10 周的结果。

回答by user3496574

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

我知道这是旧的，但我遇到了这个并认为我会添加我的代码。（建立自 rvalerio 的回答）

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

回答by guitarthrower

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

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

回答by sdanse

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.

这里有一些聪明的答案，但问题确实说，“例如 03-Mar-13 将给出三月的第 1 周。” 此处提供的答案将返回该日期的第 2 周，因为 3 月 1 日是一周的第 6 天，这使得 3 月 3 日再次成为第 1 天，因此是第 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).

换句话说，guitarthrower、rvalerio 和 user3496574 给出的方法相当于计算月历上的行数，而不是从第一天开始计算整周。所以结果可以上升到 6，而如果你按整周计算，你只能上升到 5（而 2 月显然只有 4 个整周）。

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.

这取决于您想如何计算周数。我不知道这两种方式是对还是错。但从技术上讲，如果您想将周数计为从每月 1 日开始的 7 天块——这就是我想做的——那么您需要得到当月的某一天，除以 7，然后四舍五入向上。

So in Excel it is something like:

所以在Excel中它是这样的：

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

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

VBA 没有内置的向上取整功能，但显然这会起作用：

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:

为了尽可能完整，这里有一个稍长但不访问 Excel 函数的解决方案，并且由于它使用相当简单的逻辑，如果涉及更多数据可能会更有效：

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:

此外，这里是 user3496574 答案的 Excel 公式，它以另一种方式计算周数：

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

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

在某些情况下，这可能比 VBA 版本更快。

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 discussionof some user-contributed rounding functions in VBA, but the answers are inconsistent. There is also a C++ discussion of rounding up onlythat is not hard to follow. But I think that the method above is fairly to-the-point.

Stack Overflow提供了对 VBA 中一些用户贡献的舍入函数的讨论，但答案不一致。也有一个C++ 的关于只取整的讨论并不难理解。但我认为上面的方法是中肯的。

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).

如果您确实想在 VBA 中四舍五入，那么请注意不要试图对已经四舍五入的整数（可能不是您想要的方式）进行四舍五入。

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 differentlyfrom Int (and Fix), which truncate the fractional part.

还要注意的是，可能是试图迷惑我们，VBA的Round函数使用银行家舍入（圆半连），而Excel的四舍五入没有-而且也不CINT，这也是工作方式不同，从INT（和修复），其截小数部分。

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.

我不认为使用 Banker 的四舍五入是一个明智的工程决定，但这是他们决定使用的。这种四舍五入可以向上或向下舍入 0.5 部分，让我想起今天的汽车如何试图超越我们。例如，如果我的挡风玻璃刮水器打开，而我换到倒档，那么后刮水器继续工作，乍一看似乎是电气故障，而不是功能。银行家四舍五入的原因是为了消除在您尝试对一大堆数字进行四舍五入时累积的向上偏差。

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:

所以在 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.

因此，对于 13 年 3 月 3 日，不是取 3 并除以 7，而是取 8 并除以 7。所以我现在得到 2 而不是 1。

In VBA:

在 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 methodsfor getting the first day of the month.

Exceljet 提供了这两种优雅的方法来获取每月的第一天。

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:

最后，如果你只想为了这些目的在 VBA 中做一些非常快速和肮脏的围捕，这里有几种值得黑客攻击的丑陋方法。第一的：

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.

舍入部分只有一行代码。它截断数字的小数点部分，但首先减去一点，以便第 1 天到第 7 天给出相同的结果，然后添加一天，以便我们从 1 开始而不是从 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.)

这利用了这样一个事实，即对 VBA 来说，一天只是没有小数点部分的日期值。（再次注意，为了惹恼我们，在这种情况下，VBA 和 Excel 将为 DAY 返回不同的结果。）

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 golfingor anything.

正如我提到的， Int 也会截断，但可以使用的另一个技巧是 CBool​​ 和布尔值将 0 视为假，而将分数视为真，然后可以将布尔值转换回整数——因此布尔逻辑可以用于将分数转换为整数。并不是说我们在打高尔夫球或其他什么。

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.

该函数和我上面列出的原始 RoundUpVBA 函数模仿 Excel ROUNDUP 函数，该函数使用round-away-from-zero。相比之下，Excel 的 ROUND 使用四舍五入。

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.

我在这里提到的快速单行舍入方法并不是那么优雅，它之所以有效只是因为我们知道该数字是正数，并且我们可以得到的数字的最小小数部分约为 0.14（远大于0.0001）。如果您要将四舍五入功能分离为通用功能，那么最好将其做对，以避免以后出现麻烦。但是如果算法只是包含在这个函数中，并且被正确地标记为丑陋而不是优雅和通用，那么我相信没关系。

Famous last words.

著名遗言。

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

我想知道“著名的遗言”是否曾经是任何人的著名遗言。

回答by rvalerio

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

• 计算 week1 = MONTH 的第一周
• 计算 week2 = MONTH 第 N 年的第几周
• 预期结果 = 周 2 - 周 1 + 1

Does this help?

这有帮助吗？