如何计算休息时间延长的时间

好的。下面的代码在过程中每次都进行计算。要测试它，请拿一张空白工作表，在 B2 中输入开始时间，在 C2 中输入结束时间，然后运行代码。在标准代码模块中安装代码。标准代码模块的默认名称为Module1. 它不是新工作簿中可用的模块。你将不得不添加它。（在 VBE 屏幕左侧的 Project Explorer 窗口中右键单击 VBA 项目，然后选择Insert和Module。）

Option Explicit

Enum Nws                                ' worksheet navigation
    ' 15 Jan 2018
    NwsFirstResultRow = 4
    NwsCaption = 1                      ' 1 = column A
    NwsStart                            ' each period's start time
    NwsEnd                              ' 3 = C
End Enum

Sub CalculateRestTimes()
    ' 15 Jan 2018

    Const StartTime As String = "B2"    ' change as required
    Const EndTime As String = "C2"      ' change as required
    Const BreakTime As Long = 5
    Const WorkPeriod As Long = 45

    Dim Tstart As Double, Tend As Double
    Dim Twork As Double, Tbreak As Double
    Dim Rng As Range
    Dim R As Long
    Dim p As Integer

    Twork = Round(WorkPeriod / 1440, 6)
    Tbreak = Round(BreakTime / 1440, 6)
    Application.ScreenUpdating = False
    With Worksheets("RestTimes")        ' change as required
        Tstart = Round(Val(.Range(StartTime).Value2), 6)
        Tend = Round(Val(.Range(EndTime).Value2), 6)
        If Tstart = 0 Or Tend = 0 Then
            MsgBox "Start or end time is not entered correctly.", _
                   vbCritical, "Invalid or missing entry"
        End If

        R = Application.Max(.Cells(.Rows.Count, NwsCaption).End(xlUp).Row, NwsFirstResultRow)
        Set Rng = Range(.Cells(NwsFirstResultRow, NwsCaption), .Cells(R, NwsEnd))
        Rng.ClearContents
        R = NwsFirstResultRow - 1

        Do While Tstart < Tend
            R = R + 1
            p = p + 1
            .Cells(R, NwsCaption).Value = Ordinal(p) & " period"
            .Cells(R, NwsStart).Value2 = Tstart
            Tstart = Application.Min((Tstart + Twork), Tend)
            .Cells(R, NwsEnd).Value2 = Tstart
            If (Tstart + (20 / 1440)) <= Tend Then
                R = R + 1
                .Cells(R, NwsCaption).Value = Ordinal(p) & " rest"
                .Cells(R, NwsStart).Value2 = Tstart
                Tend = Tend + Tbreak
                Tstart = Tstart + Tbreak
                .Cells(R, NwsEnd).Value2 = Tstart
            Else
                .Cells(R, NwsEnd).Value2 = Tend
                Exit Do
            End If
        Loop

        Set Rng = Range(.Cells(NwsFirstResultRow, NwsStart), .Cells(R, NwsEnd))
        Rng.NumberFormat = "HH:mm"
    End With
    Application.ScreenUpdating = True
End Sub

Private Function Ordinal(ByVal n As Integer) As String
    ' 13 Jan 2018

    Dim Suff As Variant
    Dim i As Integer

    Suff = Array("th", "st", "nd", "rd")
    i = n Mod 10
    Ordinal = CStr(n) & Suff(IIf(i > 3, 0, i))
End Function