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

Question

考虑到开始时间和结束时间。

就像8:30- 10:30，所以我可以计算持续时间(分钟)->120。

情况:每45分钟一次，休息5分钟。

时间为8:30-9:15 (第一节课)(45分钟)、9:15-9:20(1次休息时间)、9:20-10:05 (2节课)(90分钟累积)、10:05-10:10(2次休息时间)、10:10-10:40 (3节课)(提醒时间30分钟=120-90分钟)，没有第三次休息。

i只想输出: 8:30 -10:40(开始到结束)&每个休息时间(开始和结束)：9:15到9:20，10:10-10:40

是否有excel或VBA解决方案来执行此操作？

样本输入可以是: 830-12:34 ,930-1732，9:30-23:67

我目前的工作：(总分钟158分钟) 8:30-11:08首先计算每分钟的持续时间。

​

​

输入:830，11:08

输出:图片中的红线:新的结束时间，每个休息时间开始，每个休息时间结束

Answer 1

好的。下面的代码计算过程中的每一次。要测试它，请使用一个空白工作表，在B2中输入一个开始时间，在C2中输入一个结束时间，然后运行代码。将代码安装在标准代码模块中。标准代码模块的默认名称为Module1。在新的工作簿中没有一个模块可用。你得把它加进去。(右击VBE屏幕左侧project窗口中的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

Answer 2

将计算下列用户定义的函数(不包括中断)。

Function getTime(s, e)
    Dim wf As WorksheetFunction
    Dim myTime As Date, t As Date
    Dim i As Integer, n As Integer
    Dim Other()

    Set wf = WorksheetFunction
    If s > e Then
        myTime = e - s + 1
        s = 0
    Else
        myTime = e - s
    End If
    t = s + TimeSerial(0, 45, 0)
    Do
        n = n + 2
        ReDim Preserve Other(1 To n)
        Other(n - 1) = t
        Other(n) = t + TimeSerial(0, 5, 0)
        t = t + TimeSerial(0, 50, 0)
    Loop While e > t
    With wf
        For i = LBound(Other) To UBound(Other) Step 2
            If Other(i + 1) > e Then Exit For
            myTime = myTime - (.Min(e, Other(i + 1)) - .Max(s, Other(i)))
        Next i
    End With
    getTime = myTime * 1440
End Function

​

​

休息时间到了。

Sub test()
 Dim s, e

 s = Range("b9")
 e = Range("B27")

 getTimetable s, e, Range("b10")

End Sub
Sub getTimetable(s, e, target As Range)
    Dim wf As WorksheetFunction
    Dim myTime As Date, t As Date
    Dim i As Integer, n As Integer
    Dim Other()

    Set wf = WorksheetFunction
    If s > e Then
        myTime = e - s + 1
        s = 0
    Else
        myTime = e - s
    End If
    t = s + TimeSerial(0, 45, 0)
    Do
        n = n + 2
        ReDim Preserve Other(1 To n)
        Other(n - 1) = t
        Other(n) = t + TimeSerial(0, 5, 0)
        t = t + TimeSerial(0, 50, 0)
    Loop While e > t
    With wf
        For i = LBound(Other) To UBound(Other) Step 2
            If Other(i + 1) > e Then Exit For
            myTime = myTime - (.Min(e, Other(i + 1)) - .Max(s, Other(i)))
        Next i
    End With
    If n Then
        target.Resize(n) = WorksheetFunction.Transpose(Other)
    End If

End Sub

​

​

Answer 3

请尝试这个公式，其中开始时间在B2中，结束时间在C2中。该公式将返回一个新的结束时间延长5分钟，每45 minut4es的原始工作时间。

=$C2+(INT(($C2+IF($B2>$C2,1,0)-$B2)*1440/45)*5/1440)

我认为，如果在净工作时间内有9次或更多的中断，则公式应该返回错误的结果。如果这是真的，如果这是一个问题，它可以调整。