在Excel中显示周数 [英] Display Week Number in excel
问题描述
我正在写一个公式来显示从星期一开始的一个月(一周)中的WeekNumber.但是在月末，如果星期从星期一开始，而下一个日期在下个月，则周号应显示为下个月的第一周.例如:周从星期一开始，因此，如果星期一是8月31日，并且剩余日期(即星期二，星期二)在下个月，则周号应显示为1，并且如果星期一，星期二，星期三和星期四是该月的最后几天及之后天，即星期五至星期日是下个月，则周号应显示为W5或上周的那个月.
I am writing a formula to display the WeekNumber in a month, week starting from Monday. But at the month end if the week starts at Monday and following dates are in next month then the week number should be displayed as the next month first week. For example: Week Starts on Monday, so if Monday is 31st Aug and remaining days i.e Tue to Sun are in next month then the Week Number should display as 1 and if Monday, Tue, Wed and Thurs are last days of the month and following days i.e Friday to Sun are in next month then Week number should be displayed as W5 or that month last week.
我写了一个公式，但我不能满足条件.
i have written a formula but i am unable to satisfy the conditions.
="W"&INT((6+DAY(E2+1-WEEKDAY(E2,2)))/7)
推荐答案
使用公式这样做没意思.
This will be no fun to do using formulas.
但是，在VBA中，使用两个泛型函数并没有那么复杂:
In VBA, however, it is not that convoluted using two generic functions:
Public Function WeekOfMonth( _
ByVal Date1 As Date) _
As Integer
Dim ThursdayInWeek As Date
Dim FirstThursday As Date
Dim WeekNumber As Integer
ThursdayInWeek = DateWeekdayInWeek(Date1, vbThursday, vbMonday)
FirstThursday = DateWeekdayInMonth(ThursdayInWeek, 1, vbThursday)
WeekNumber = 1 + DateDiff("ww", FirstThursday, Date1, vbMonday)
WeekOfMonth = WeekNumber
End Function
然后:
="W"&WeekOfMonth(E2)
完整模块(复制粘贴):
The full module (copy-paste):
Option Explicit
Public Const DaysPerWeek As Long = 7
Public Const MaxWeekdayCountInMonth As Integer = 5
' Calculates the "weeknumber of the month" for a date.
' The value will be between 1 and 5.
'
' Numbering is similar to the ISO 8601 numbering having Monday
' as the first day of the week and the first week beginning
' with Thursday or later as week number 1.
' Thus, the first day of a month may belong to the last week
' of the previous month, having a week number of 4 or 5.
'
' 2020-09-23. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function WeekOfMonth( _
ByVal Date1 As Date) _
As Integer
Dim ThursdayInWeek As Date
Dim FirstThursday As Date
Dim WeekNumber As Integer
ThursdayInWeek = DateWeekdayInWeek(Date1, vbThursday)
FirstThursday = DateWeekdayInMonth(ThursdayInWeek, 1, vbThursday)
WeekNumber = 1 + DateDiff("ww", FirstThursday, Date1, vbMonday)
WeekOfMonth = WeekNumber
End Function
' Calculates the date of DayOfWeek in the week of DateInWeek.
' By default, the returned date is the first day in the week
' as defined by the current Windows settings.
'
' Optionally, parameter DayOfWeek can be specified to return
' any other weekday of the week.
' Further, parameter FirstDayOfWeek can be specified to select
' any other weekday as the first weekday of a week.
'
' Limitation:
' For the first and the last week of the range of Date, some
' combinations of DayOfWeek and FirstDayOfWeek that would result
' in dates outside the range of Date, will raise an overflow error.
'
' 2017-05-03. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInWeek( _
ByVal DateInWeek As Date, _
Optional ByVal DayOfWeek As VbDayOfWeek = VbDayOfWeek.vbUseSystemDayOfWeek, _
Optional ByVal FirstDayOfWeek As VbDayOfWeek = VbDayOfWeek.vbUseSystemDayOfWeek) _
As Date
Dim DayInWeek As VbDayOfWeek
Dim OffsetZero As Integer
Dim OffsetFind As Integer
Dim ResultDate As Date
' Find the date of DayOfWeek.
DayInWeek = Weekday(DateInWeek)
' Find the offset of the weekday of DateInWeek from the first day of the week.
' Will always be <= 0.
OffsetZero = (FirstDayOfWeek - DayInWeek - DaysPerWeek) Mod DaysPerWeek
' Find the offset of DayOfWeek from the first day of the week.
' Will always be >= 0.
OffsetFind = (DayOfWeek - FirstDayOfWeek + DaysPerWeek) Mod DaysPerWeek
' Calculate result date using the sum of the offset parts.
ResultDate = DateAdd("d", OffsetZero + OffsetFind, DateInWeek)
DateWeekdayInWeek = ResultDate
End Function
' 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.
'
' 2019-12-08. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
ByVal DateInMonth As Date, _
Optional ByVal Occurrence As Integer, _
Optional ByVal Weekday As VbDayOfWeek = vbUseSystemDayOfWeek) _
As Date
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
' vbUseSystemDayOfWeek, zero, none or invalid value for VbDayOfWeek.
Weekday = VBA.Weekday(DateInMonth)
End Select
' Validate Occurence.
If Occurrence < 1 Then
' Find first occurrence.
Occurrence = 1
ElseIf Occurrence > MaxWeekdayCountInMonth Then
' Find last occurrence.
Occurrence = MaxWeekdayCountInMonth
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 = DaysPerWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysPerWeek) Mod DaysPerWeek
' Calculate result date.
ResultDate = DateAdd("d", Offset, ResultDate)
If Occurrence = MaxWeekdayCountInMonth 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", -DaysPerWeek, ResultDate)
End If
End If
DateWeekdayInMonth = ResultDate
End Function
示例输出-请注意，在跨过新年时，周的编号也正确:
Example output - note that weeks are numbered correctly also when crossing New Year:
这篇关于在Excel中显示周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
