有没有人有按月返回“一个月中的一周”的功能?搜索到的大部分功能都是从第一周开始。我想按星期出发,即2018年3月1日将是2月5日。三月的第一周从三月四日开始。
我怎么能这么做?
发布于 2018-03-08 07:21:23
首先查找月份日期的前一个星期日,因为这个日期可能会落在前一个月:
DateAdd("d", 1 - Weekday(DateOfMonth), DateOfMonth)然后使用这个泛型函数查找月份的第一个星期日:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
ByVal DateInMonth As Date, _
Optional ByVal Occurrence As Integer, _
Optional ByVal Weekday As VbDayOfWeek = -1) _
As Date
Const DaysInWeek As Integer = 7
Dim Offset As Integer
Dim Month As Integer
Dim Year As Integer
Dim ResultDate As Date
' Validate Weekday.
Select Case Weekday
Case _
vbMonday, _
vbTuesday, _
vbWednesday, _
vbThursday, _
vbFriday, _
vbSaturday, _
vbSunday
Case Else
' Zero, none or invalid value for VbDayOfWeek.
Weekday = VBA.Weekday(DateInMonth)
End Select
' Validate Occurence.
If Occurrence <= 0 Then
Occurrence = 1
ElseIf Occurrence > 5 Then
Occurrence = 5
End If
' Start date.
Month = VBA.Month(DateInMonth)
Year = VBA.Year(DateInMonth)
ResultDate = DateSerial(Year, Month, 1)
' Find offset of Weekday from first day of month.
Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
' Calculate result date.
ResultDate = DateAdd("d", Offset, ResultDate)
If Occurrence = 5 Then
' The latest occurrency of Weekday is requested.
' Check if there really is a fifth occurrence of Weekday in this month.
If VBA.Month(ResultDate) <> Month Then
' There are only four occurrencies of Weekday in this month.
' Return the fourth as the latest.
ResultDate = DateAdd("d", -DaysInWeek, ResultDate)
End If
End If
DateWeekdayInMonth = ResultDate
End Function最后,将这些集合起来,使用DateDiff获取周日的计数,并添加1(1)以获得周数:
MonthWeekNumber = 1 + DateDiff("w", DateWeekdayInMonth(DateAdd("d", 1 - Weekday(DateOfMonth), DateOfMonth), 1, vbSunday), DateOfMonth) https://stackoverflow.com/questions/49166301
复制相似问题