如何动态计算月内自定义工作日区间的总工时

需求说明 #

• 功能目标：根据用户输入的自定义工作日区间（如每周一至周六、每周二至周日），结合指定月份（由用户输入的日期单元格/文本框提供），自动计算该区间在当月的总天数，再乘以单日工作时长得出总工时。
• 动态更新要求：修改单元格中的区间描述（如改为每周三至周一）时，需自动刷新当月天数统计与总工时计算。
• 示例场景：
  • 2023年1月符合每周一至周六的区间为：2-7、9-14、16-21、23-28、30-31
  • 2022年11月符合每周一至周六的区间为：1-5、8-12、15-19、22-26、29-30

原代码问题分析 #

你提供的代码存在以下关键缺失：

• 未解析区间字符串中的起止星期（比如从every Monday to Saturday中提取周一和周六）
• 未定义nb_days变量，无法关联当月日期计算
• 缺少当月日期遍历与区间匹配逻辑
• 未实现结果写入单元格的逻辑

完整实现代码 #

以下是补全后的VBA代码，包含字符串解析、日期遍历、工时计算及自动更新功能：

' 工作表变更事件：当Input表的区间或日期单元格修改时自动计算
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsInput As Worksheet
    Set wsInput = ThisWorkbook.Sheets("Input")
    
    ' 仅监听B列（区间）、C列（日期）、D列（单日时长）的修改
    If Not Intersect(Target, wsInput.Range("B:C,D:D")) Is Nothing Then
        CalculateMonthlyWorkHours wsInput
    End If
End Sub

' 核心计算函数
Sub CalculateMonthlyWorkHours(ws As Worksheet)
    Dim lastRow As Long
    Dim x As Long
    Dim intervalStr As String
    Dim startDay As String, endDay As String
    Dim targetDate As Date
    Dim firstDayOfMonth As Date, lastDayOfMonth As Date
    Dim currentDate As Date
    Dim workDayCount As Integer
    Dim dailyHours As Double, totalHours As Double
    
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    For x = 2 To lastRow
        intervalStr = Trim(ws.Cells(x, 2).Value)
        targetDate = ws.Cells(x, 3).Value
        dailyHours = ws.Cells(x, 4).Value
        
        ' 跳过无效输入
        If IsDate(targetDate) = False Or dailyHours <= 0 Or InStr(1, UCase(intervalStr), "TO") = 0 Then
            ws.Cells(x, 5).Value = "" ' 清空结果列
            Continue For
        End If
        
        ' 解析区间的起止星期
        Dim splitParts() As String
        splitParts = Split(intervalStr, " ")
        ' 处理两种格式："every X to Y" 或直接 "X to Y"
        If UCase(splitParts(0)) = "EVERY" Then
            startDay = splitParts(1)
            endDay = splitParts(3)
        Else
            startDay = splitParts(0)
            endDay = splitParts(2)
        End If
        
        ' 转换星期为数字（周一=1，周日=7）
        Dim startWeekday As Integer, endWeekday As Integer
        startWeekday = GetWeekdayNumber(startDay)
        endWeekday = GetWeekdayNumber(endDay)
        
        If startWeekday = 0 Or endWeekday = 0 Then
            ws.Cells(x, 5).Value = "无效星期格式"
            Continue For
        End If
        
        ' 获取目标月份的第一天和最后一天
        firstDayOfMonth = DateSerial(Year(targetDate), Month(targetDate), 1)
        lastDayOfMonth = DateSerial(Year(targetDate), Month(targetDate) + 1, 0)
        
        ' 遍历当月每一天，统计符合区间的天数
        workDayCount = 0
        currentDate = firstDayOfMonth
        Do While currentDate <= lastDayOfMonth
            Dim currentWeekday As Integer
            currentWeekday = Weekday(currentDate, vbMonday) ' 周一=1，周日=7
            
            ' 判断当前日期是否在工作日区间内
            If startWeekday <= endWeekday Then
                ' 区间是连续的（如周一至周六：1-6）
                If currentWeekday >= startWeekday And currentWeekday <= endWeekday Then
                    workDayCount = workDayCount + 1
                End If
            Else
                ' 区间跨周日（如周三至周一：3-1，即周三、周四、周五、周六、周日、周一）
                If currentWeekday >= startWeekday Or currentWeekday <= endWeekday Then
                    workDayCount = workDayCount + 1
                End If
            End If
            
            currentDate = currentDate + 1
        Loop
        
        ' 计算总工时并写入E列
        totalHours = workDayCount * dailyHours
        ws.Cells(x, 5).Value = totalHours
        ' 可选：写入统计的天数到F列
        ws.Cells(x, 6).Value = workDayCount
    Next x
End Sub

' 辅助函数：将星期名称转换为数字（周一=1，周日=7）
Function GetWeekdayNumber(dayName As String) As Integer
    Select Case UCase(dayName)
        Case "MONDAY": GetWeekdayNumber = 1
        Case "TUESDAY": GetWeekdayNumber = 2
        Case "WEDNESDAY": GetWeekdayNumber = 3
        Case "THURSDAY": GetWeekdayNumber = 4
        Case "FRIDAY": GetWeekdayNumber = 5
        Case "SATURDAY": GetWeekdayNumber = 6
        Case "SUNDAY": GetWeekdayNumber = 7
        Case Else: GetWeekdayNumber = 0 ' 无效输入
    End Select
End Function

使用说明 #

1. 将代码粘贴到Input工作表的代码模块中（右键工作表标签→查看代码）
2. 工作表列布局建议：
   • B列：输入工作日区间（支持every Monday to Saturday或Monday to Saturday格式）
   • C列：输入目标月份的任意日期（如2023/1/1）
   • D列：输入单日工作时长（如8）
   • E列：自动输出总工时
   • F列：自动输出当月符合区间的天数
3. 修改B/C/D列内容时，E/F列会自动更新计算结果

内容的提问来源于stack exchange，提问作者user21100377