You need to enable JavaScript to run this app. 如何在现有Excel VBA薪资代码中加入适配排班规则的假期工时逻辑
优惠活动
大模型
产品
解决方案
定价
更多

如何在现有Excel VBA薪资代码中加入适配排班规则的假期工时逻辑

薪资系统工时统计VBA开发需求

业务规则说明

  • 系统需分别统计三类工时:工作日工时、周末工时、假期工时
  • 班制时间定义:
    • 日班:指定排班当日06:00至22:59
    • 夜班:排班当日23:00至次日05:59
  • 假期列表已存储为数组,需匹配员工出勤日期完成假期工时匹配计算

统计规则示例

  • 业务场景:某员工12月25日(圣诞节,周五,假期)22:00开始工作,当日22:50结束;12月26日(周六,假期)01:00开始工作,当日02:45结束
  • 统计要求:
    • 22:00-22:50对应的00:59小时工时,计入假期日班
    • 01:00-02:45对应的1小时45分钟工时,归属12月25日(圣诞节)排班对应的假期夜班

现有开发状态

已完成VBA代码编写,可完美适配工作日、周末的排班工时计算逻辑,但无法适配假期统计规则,现有代码如下:

Sub Test()

    PayRoleTime "11/27/2020  10:00:00 PM", "11/28/2020 10:00:00 AM"

End Sub

Public Function PayRoleTime(startTime As Date, endTime As Date)
'Declare Variables
    Dim startDbl, startHour, startMin, startHourMin As Double
    Dim endDbl, endHour, endMin, endHourMin As Double
    Dim currentDbl, currentHour, currentMin, currentHourMin As Double
    Dim workWeekDay(1) As Double
    Dim nightShift(1) As Double
    Dim startingDay As Double
    Dim output As Variant
    Dim currentWeekday As Integer
    
'Set Variables for Company Shift Structure
    workWeekDay(0) = 2
    workWeekDay(1) = 6
    nightShift(0) = 23
    nightShift(1) = 6
    ReDim output(5)

'Set Variables from Inputs
    startDbl = CDbl(startTime) + 0.00000033
    startingDay = Int(startDbl)
    startDay = weekDay(startingDay)
    startHour = (startDbl - Int(startDbl)) * 24
    startMin = Int((startHour - Int(startHour)) * 60)
    startHour = Int((startDbl - Int(startDbl)) * 24)
    startHourMin = startHour + (startMin / 100)
    
    endDbl = CDbl(endTime) + 0.00000033
    endingDay = Int(endDbl)
    endDay = weekDay(endingDay)
    endHour = (endDbl - Int(endDbl)) * 24
    endMin = Int((endHour - Int(endHour)) * 60)
    endHour = Int((endDbl - Int(endDbl)) * 24)
    endHourMin = endHour + (endMin / 100)
    
    currentDbl = startingDay
    currentHour = startHour
    currentMin = startMin
    currentHourMin = startHourMin
'Loop to get working hour times
    Do While (currentDbl < endingDay Or currentHour < endHour Or currentMin < endMin)
            currentWeekday = weekDay(currentDbl)
            currentHourMin = currentHour + (currentMin / 100)
            
        If currentWeekday >= workWeekDay(0) And currentWeekday <= workWeekDay(1) Then
            If currentHourMin >= nightShift(0) Or currentHourMin < nightShift(1) Then
                If currentWeekday = workWeekDay(0) And currentHourMin < nightShift(1) Then
                    output(5) = output(5) + 1 'Increment Sunday Night Shift
                Else
                    output(1) = output(1) + 1 'Increment Weekday Night Shift
                End If
            Else
                output(0) = output(0) + 1 'Increment Weekday Day Shift
            End If
        ElseIf currentWeekday < workWeekDay(0) Then
            If currentHourMin >= nightShift(0) Or currentHourMin < nightShift(1) Then
                If currentWeekday < workWeekDay(0) And currentHourMin < nightShift(1) Then
                    output(3) = output(3) + 1 'Increment Saturday Night Shift
                Else
                    output(5) = output(5) + 1 'Increment Sunday Night Shift
                End If
            Else
                output(4) = output(4) + 1 'Increment Sunday Day Shift
            End If
        ElseIf currentWeekday > workWeekDay(1) Then
            If currentHourMin >= nightShift(0) Or currentHourMin < nightShift(1) Then
                If currentWeekday > workWeekDay(1) And currentHourMin < nightShift(1) Then
                    output(1) = output(1) + 1 'Increment Weekday Night Shift
                Else
                    output(3) = output(3) + 1 'Increment Saturday Night Shift
                End If
            Else
                output(2) = output(2) + 1 'Increment Saturday Day Shift
            End If
        End If

        'Increment Time
            currentMin = currentMin + 1
            If currentMin = 60 Then
                currentMin = 0
                currentHour = currentHour + 1
            End If
            If currentHour = 24 Then
                currentHour = 0
                currentDbl = currentDbl + 1
            End If

    Loop
    
   
    
'Print Results to Immediate Window
    Debug.Print "Employee worked " & output(0) / 60 & " Hours, Weekday Day Shift"
    Debug.Print "Employee worked " & output(1) / 60 & " Hours, Weekday Night Shift"
    Debug.Print "Employee worked " & output(2) / 60 & " Hours, Saturday Day Shift"
    Debug.Print "Employee worked " & output(3) / 60 & " Hours, Saturday Night Shift"
    Debug.Print "Employee worked " & output(4) / 60 & " Hours, Sunday Day Shift"
    Debug.Print "Employee worked " & output(5) / 60 & " Hours, Sunday Night Shift"
End Function

内容的提问来源于stack exchange,提问作者Arun Singh G

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.08 00:45:04