Блог

Я пишу формулу для отображения номера недели за месяц, неделя начинается с понедельника. Но в конце месяца, если неделя начинается с понедельника, а следующие даты относятся к следующему месяцу, тогда номер недели должен отображаться как первая неделя следующего месяца. Например: неделя начинается в понедельник, поэтому, если понедельник 31 августа, а оставшиеся дни, т.е. с вторника по Вс, относятся к следующему месяцу, тогда номер недели должен отображаться как 1, и если понедельник, вт, Ср и чт являются последними днями месяца, а последующие дни, т.е. с пятницы по Вс, являютсяв следующем месяце номер недели должен отображаться как W5 или этот месяц на прошлой неделе.

я написал формулу, но не могу выполнить условия.

 ="W"amp;INT((6 DAY(E2 1-WEEKDAY(E2,2)))/7)
  

Это будет неинтересно делать с помощью формул.

Однако в VBA это не так сложно, используя две общие функции:

 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"amp;WeekOfMonth(E2)
  

Полный модуль (копирование-вставка):

 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
  

Пример вывода — обратите внимание, что недели пронумерованы правильно и при пересечении Нового года:

Редактирование # 1

Основываясь на ваших комментариях, вы можете напрямую использовать формулу, указанную в приведенной выше ссылке, чтобы получить результат, как показано ниже:

 =ISOWEEKNUM(E2)-ISOWEEKNUM(DATE(YEAR(E2),MONTH(E2),1)) 1
  

Старый ответ:

Заимствование формулы, приведенной в комментариях (https://thesmartmethod.com/an-excel-formula-to-get-the-week-of-month /), вы можете использовать следующую формулу:

 =IF(AND(WEEKDAY(E2,2)=1, DAY(E2 1)=1),1,ISOWEEKNUM(E2)-ISOWEEKNUM(DATE(YEAR(E2),MONTH(E2),1)) 1)
  

Предполагая, что ваше единственное условие — если первый день недели — понедельник предыдущего месяца, а с вторника по воскресенье — в следующем месяце