我知道這個問題已經以許多不同的形式被提出，但我想展示我的情況，因為我還沒有找到完美的解決方案。
因此，我需要做的是將每個月分成4或5周，并將其輸入到相應的單元格中。
示例 :
我已經試過這個例子了。
我嘗試了用戶：danieltakeshi在這個主題中寫的這個示例代碼：
請幫助我找到一個解決方案，或者告訴我如何使它適應我目前的情況。
Cordially,
uj5u.com熱心網友回復：
這個程式檢查第一個和最后一個作業日（周一至周五），然后給出該日期范圍內的日歷周數
Option Explicit
Public Sub test_getWeeknumbersForMonth()
Dim arr As Variant
arr = getWeekNumbersForMonth("1.10.2021"/span>)
Debug.Print "1.10.2021: ", Join（arr, " - "）。
arr = getWeekNumbersForMonth("1.1.2022"/span>)
Debug.Print "1.1.2022: ", Join(arr, " - ")
結束 sub
Public Function getWeekNumbersForMonth(inputDate As Date) As Variant
Dim datStart As Date
datStart = getFirstWorkingDayOfMonth(inputDate)
Dim datEnd As Date
datEnd = getLastWorkingDayOfMonth(inputDate)
Dim arrWeekNumbers As Variant
ReDim arrWeekNumbers(1 To 6) '最多6周可以退貨。
Dim i As Long: i = 1
Dim dat As Date
dat = datStart
While dat <= datEnd
arrWeekNumbers(i) = getCalendarWeek(dat)
i = i 1
dat = DateAdd("ww"/span>, 1, dat)
補上
ReDim Preserve arrWeekNumbers(i - 1)
getWeekNumbersForMonth = arrWeekNumbers
結束 功能
Private Function getFirstWorkingDayOfMonth(inputDate As Date) As Date Date
Dim datToCheck As Date: datToCheck = DateSerial（Year(inputDate), Month(inputDate), 1) - 1
Dim isWorkingday As Boolean
Do
datToCheck = datToCheck 1
isWorkingday = Weekday(datToCheck, vbMonday) <=5
Loop Until isWorkingday = True
getFirstWorkingDayOfMonth = datToCheck
End Function[/span
Private Function getLastWorkingDayOfMonth(inputDate As Date) As Date Date
Dim datToCheck As Date: datToCheck = DateSerial(Year(inputDate), Month(inputDate) 1, 1)
Dim isWorkingday As Boolean
Do
datToCheck = datToCheck - 1
isWorkingday = Weekday(datToCheck, vbMonday) <=5
Loop Until isWorkingday = True
getLastWorkingDayOfMonth = datToCheck
End Function[/span
Private Function getCalendarWeek(inputDate As Date) As Long Long
'European iso week - CW 1 = week with first thursday[/span]。
getCalendarWeek = Application.WorksheetFunction.IsoWeekNum(inputDate)
'使用weeknum-function -根據你的需要調整第二個引數。
'https://support.microsoft.com/en-us/office/weeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340。
'getCalendarWeek = Application.WorksheetFunction.WeekNum(inputDate, 2)
結束 功能
uj5u.com熱心網友回復：
首先，有些月份的日期是六周。
其次，VBA本身不能回傳正確的ISO 8601周數：
最后，周數并不關心作業日或周末。如果你想排除那些不包括特定作業日的周數，請對這些日期進行過濾。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/323575.html
標籤：
下一篇：打開Excel檔案時"無回應"。