vba 获取给定日期的周数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1649720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the week number from a given date
提问by Nick Dandoulakis
Examples:
例子：
'DD/MM/YYYY
"1/1/2009" should give `1`
"31/1/2009" should give `5`
"1/2/2009" should also give `5`
Format("1/2/2009", "ww")returns 6.
Format("1/2/2009", "ww")返回6。
So, how can I get the correct result?
那么，我怎样才能得到正确的结果呢？
回答by AdamV
It's doing two things here which don't match your expectations, I think: Assuming you want the week with Jan 1 in as week 1, and using Sunday as first day of the week So it has week 1 running from Sunday 28th December 2008 to Saturday 3rd Jan 2009.
它在这里做了两件与您的期望不符的事情，我认为：假设您希望将 1 月 1 日作为第 1 周，并使用星期日作为一周的第一天，因此第 1 周从 2008 年 12 月 28 日星期日到2009 年 1 月 3 日星期六。
Week 6 would begin on Sunday 1st Feb by this method.
通过这种方法，第 6 周将从 2 月 1 日星期日开始。
The ISO standard is for week 1 to be the one containing 4 days of January, or the first Thursday of the year (different ways of expressing the same thing). You can specify this method of calculation and the first day of the week:
ISO 标准将第 1 周设为包含 1 月 4 天或一年中的第一个星期四（表达同一事物的不同方式）的第 1 周。您可以指定此计算方法和一周的第一天：
Format(SomeDate,"ww",vbMonday,vbFirstFourDays)
see here for syntax:
语法见这里：
https://support.office.com/en-US/article/Format-Function-6F29D87B-8761-408D-81D3-63B9CD842530
https://support.office.com/en-US/article/Format-Function-6F29D87B-8761-408D-81D3-63B9CD842530
回答by UpTheCreek
This might work: Format(YourDate, "ww",vbMonday)
这可能有效： Format(YourDate, "ww",vbMonday)
回答by David-W-Fenton
Regardless of the day of the week your week starts on, you need to pass unambiguous date values. "31/1/2009" can only be one date (Jan 31st), but "1/2/2009" could be Jan. 2 (US style) or Feb. 1st (everybody else who has more sense that we USAns).
无论一周的哪一天开始，您都需要传递明确的日期值。“31/1/2009”只能是一个日期（1 月 31 日），但“1/2/2009”可以是 1 月 2 日（美国风格）或 2 月 1 日（其他比我们美国人更有感觉的人）。
In this case, I'd use DateSerial() to make sure the date is not misinterpreted:
在这种情况下，我会使用 DateSerial() 来确保日期不会被误解：
Format(DateSerial(2009,2,1), "ww", vbMonday)
While this is not causing your problem, because Access helpfully utilizes your system's localized date settings, I think it's something you should do anyway. You certainly are forced to do so in SQL in Access, so I don't think it's a bad habit in code and expressions.
虽然这不会导致您的问题，因为 Access 有助于利用您系统的本地化日期设置，但我认为无论如何您都应该这样做。你肯定是被迫在 Access 中的 SQL 中这样做的，所以我不认为这在代码和表达式中是一个坏习惯。
回答by Henk Holterman
"Correct result" depends on the locale. Maybe VBA will let you pick a calendar-system, otherwise you're pretty much out of luck.
“正确结果”取决于语言环境。也许 VBA 会让你选择一个日历系统，否则你就很不走运了。
Note that First-Day-On-xxDay isn't your only problem. There is also variation on what a complete week is so Week 1in one system could be Week 53of the previous year in another system.
请注意，First-Day-On-xxDay 并不是您唯一的问题。完整的一周也有变化，因此一个系统中的第 1周可能是另一个系统中前一年的第 53 周。
So test thoroughly and don't be seduced to "correct by 1".
所以要彻底测试，不要被诱惑“纠正 1”。
回答by amartynov
There is a whole standard for week numbers: ISO-8601
周数有一个完整的标准：ISO-8601
回答by dbDesigner
I had the same problem.
我有同样的问题。
It showed week 53 and week 1, yet days in week 53 and week 1 are all in week 1
它显示了第 53 周和第 1 周，但第 53 周和第 1 周的天数都在第 1 周
I first tried changing the date format in the Access Query to this:
我首先尝试将 Access Query 中的日期格式更改为：
OrderWeek: Format([OrderDate],"yyyy-ww",1,3) <-- But it did not do the trick. You get dates like 2014-52 for week 52 and 2015-52 where it was week 1 before.
OrderWeek: Format([OrderDate],"yyyy-ww",1,3) <-- 但它没有成功。您会在第 52 周和 2015-52 中获得 2014-52 之类的日期，这是第 1 周之前的日期。
Also the sorting was not how I liked. It sorted the data as 2014-1, 2014-11, 2014-2 etc. I want it to show as 2014-01, 2014-02 .. 2014-11 etc.
排序也不是我喜欢的方式。它将数据排序为 2014-1、2014-11、2014-2 等。我希望它显示为 2014-01、2014-02 .. 2014-11 等。
So here is the new code to display both the year and the week correctly in an Access Query:
所以这里是在 Access Query 中正确显示年份和星期的新代码：
ActualWeek: IIf(DatePart("ww",[SomeDate])=53,DatePart("yyyy",[SomeDate])+1,DatePart("yyyy",[SomeDate])) & "-" & IIf(DatePart("ww",[SomeDate])=53,"01",IIf(DatePart("ww",[SomeDate])<10,"0" & DatePart("ww",[SomeDate]),DatePart("ww",[SomeDate])))
This now shows any days from week 53 as being part of week 1
这现在将第 53 周的任何天显示为第 1 周的一部分
回答by Fedearne
If sunday is the first day of the week (as it is in some locales) then 6 is the correct weeknumber for "1/2/2009" (february 1. 2009)
如果星期日是一周的第一天（在某些语言环境中是这样），那么 6 是“1/2/2009”（2009 年 2 月 1 日）的正确周数
