1. A2：『=IF(MONTH($F$1)=MONTH($F$1+(ROW(A2)-2)),$F$1+(ROW(A2)-2),"")』
2. B2：『=IF(A2="","",RIGHT(TEXT(WEEKDAY(A2),"[$-404]aaaa;@"),1))』
3. C2：『=IF(A2="","",IF(OR(B2="六",B2="日"),IF(OR(ISERR(SEARCH("補",D2))),"否","是"),IF(OR(ISERR(SEARCH("休",D2))),"是","否")))』
4. 統一往下拉至第32欄
5. G2：『=COUNT($A$2:$A$32)』
6. G3：『=COUNTIF($C$2:$C$32,"是")』
7. G4：『=COUNTIF($C$2:$C$32,"否")』
※欄位數雖然達到32欄，但若該月沒有31日，則會自動顯示為空白。
※D欄的項目以『補』為關鍵字判定是否要補班、以『休』為關鍵字判定是否要休假。
同樣附個附件好了：[點擊下載]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 6 And Target.Row = 1 Then
Dim i As Integer, d As Integer
For i = 2 To 32
Range("A" & i).Clear
Next
d = Day(DateAdd("M", 1, Range("F1")) - 1)
Range("G2") = d
For i = 1 To d
Range("A" & i + 1) = Format(Range("F1"), "yyyy/mm/" & i)
Next
End If
End Sub
內文搜尋
從 APP 打開
X