如何在现有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
相关产品推荐
相关产品推荐

