跨日工作时长计算：Excel薪资计算器VBA代码优化需求

• 开发Excel薪资计算器，单元格F3决定正常工时（regular hours）、加班工时（OT hours）、**双倍加班工时（DT hours）**的计算逻辑，打卡时间存于D10，签退时间存于F10。
• 现有计算存在以下问题：
  • 早班、中班跨日工作场景（如中班加班到次日）计算出现负数；
  • 晚班工时完全计算错误；
  • 早班提前在前一天23点上班并工作至正常签退时间时计算异常。
• 当前仅调试周一的计算模块，规则明确：当F3=1时，正常工时为6:00-14:00（允许迟到），超出正常工时的前2小时计为OT（最多2小时），超出OT的部分计为DT，多班次嵌套逻辑复杂。
• 备注：下载Excel表时忽略“standard hours”字段，其仅作参考，现有OT/DT计算不符合合同规则。

现有VBA代码 #

Public Sub Monday()
    Dim startTime As Date
    Dim endtime As Date
    Dim regHours As Double
    Dim otHours As Double
    Dim dtHours As Double

    ' Convert the time values to dates
     startTime = CDate(Range("D10"))
     endtime = CDate(Range("F10"))

    ' Calculate the total hours worked
     Dim totalHours As Double
     totalHours = (endtime - startTime) * 24
    
    ' Determine the time period
     timePeriod = Range("F3").Value
    
    ' Debugging output
     Debug.Print "Row: " & 10
     Debug.Print "Start time: " & startTime
     Debug.Print "End time: " & endtime
     Debug.Print "Total hours: " & totalHours

    ' Calculate the regular hours worked
    If timePeriod = 1 And Hour(startTime) >= 6 And Hour(endtime) <= 14 Then
            regHours = totalHours
    ElseIf timePeriod = 2 And Hour(startTime) >= 14 And Hour(endtime) <= 22 Then
            regHours = totalHours
    ElseIf timePeriod = 3 And ((Hour(startTime) >= 22 And Hour(endtime) <= 23) Or (Hour(startTime) >= 0 And Hour(endtime) <= 6)) Then
            regHours = totalHours
    ElseIf timePeriod = 1 And Hour(startTime) < 6 And Hour(endtime) <= 14 Then
            regHours = (Hour(endtime) - 6 + Minute(endtime) / 60)
    ElseIf timePeriod = 2 And Hour(startTime) < 14 And Hour(endtime) <= 22 Then
            regHours = (Hour(endtime) - 14 + Minute(endtime) / 60)
    ElseIf timePeriod = 3 And ((Hour(startTime) < 22 And Hour(endtime) <= 23) Or (Hour(startTime) >= 0 And Hour(endtime) < 6)) Then
            regHours = (Hour(endtime) - 22 + Minute(endtime) / 60)
    ElseIf timePeriod = 1 And Hour(startTime) < 6 And Hour(endtime) >= 14 Then
            regHours = (Hour(endtime) - 10 + Minute(endtime) / 60)
    ElseIf timePeriod = 2 And Hour(startTime) < 14 And Hour(endtime) >= 22 Then
            regHours = (Hour(endtime) - 15 + Minute(endtime) / 60)
    ElseIf timePeriod = 3 And ((Hour(startTime) < 22 And Hour(endtime) >= 23) Or (Hour(startTime) >= 0 And Hour(endtime) < 6)) Then
            regHours = (Hour(endtime) - 23 + Minute(endtime) / 60)
    ElseIf timePeriod = 1 And Hour(startTime) >= 6 And Hour(endtime) > 14 Then
            regHours = (14 - Hour(startTime) + Minute(startTime) / 60)
    ElseIf timePeriod = 2 And Hour(startTime) >= 14 And Hour(endtime) > 22 Then
            regHours = (22 - Hour(startTime) + Minute(startTime) / 60)
    ElseIf timePeriod = 3 And ((Hour(startTime) >= 22 And Hour(endtime) < 24) Or (Hour(startTime) >= 0 And Hour(endtime) <= 6)) Then
            regHours = (6 - Hour(startTime) + Minute(startTime) / 60)
    Else
            regHours = (14 - Hour(startTime) + Minute(startTime) / 60) + (Hour(endtime) - 14 + Minute(endtime) / 60)
    End If

    ' Calculate the overtime hours worked
    If totalHours > regHours And totalHours <= regHours + 2 Then
        otHours = totalHours - regHours
    ElseIf totalHours > regHours + 2 Then
        otHours = 2
    End If
    
    ' Debugging output
     Debug.Print "Regular hours: " & regHours
     
    ' Debugging output
     Debug.Print "Over Time Hours: " & otHours

    ' Calculate the double time hours worked
    If totalHours > regHours + 2 Then
        dtHours = totalHours - (regHours + 2)
    End If
    
    ' Debugging output
     Debug.Print "Double time hours: " & dtHours

    ' Output the results
     Range("H10").Value = regHours
     Range("J10").Value = otHours
     Range("L10").Value = dtHours
End Sub

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