I’ll walk you through a practical VBA solution that handles your messy input formats, calculates the adjusted end time, and lists all valid rest periods. First, a quick clarification: your example had a small typo—10:10-10:40 is a work period, not a rest one; the actual rest periods are 9:15-9:20 and 10:05-10:10. This solution will output the correct rest windows.

Core Logic #

1. Parse Input Times: Convert formats like 830, 12:34, even 23:67 into valid Excel time values.
2. Calculate Total Work Minutes: Compute the duration of the original work session (handles cross-day schedules).
3. Determine Rest Periods: Add a 5-minute break after every 45 minutes of work—only if there’s work left after the break.
4. Compute Adjusted End Time: Total break minutes are added to the original end time.
5. List Rest Periods: Generate start/end times for each break session.

VBA Code Implementation #

Open your Excel workbook, press Alt+F11 to open the VBA editor, insert a new module, and paste this code:

Option Explicit

' Converts time strings (with/without colon) to valid Excel time
Function ParseTime(timeStr As String) As Date
    Dim colonPos As Integer
    colonPos = InStr(timeStr, ":")
    
    If colonPos > 0 Then
        Dim hours As Integer, minutes As Integer
        hours = CInt(Left(timeStr, colonPos - 1))
        minutes = CInt(Mid(timeStr, colonPos + 1))
        ParseTime = TimeSerial(hours, minutes, 0)
    Else
        Dim timeNum As Integer
        timeNum = CInt(timeStr)
        hours = timeNum \ 100
        minutes = timeNum Mod 100
        ParseTime = TimeSerial(hours, minutes, 0)
    End If
End Function

' Main macro to calculate adjusted schedule
Sub CalculateWorkSchedule()
    Dim startInput As String, endInput As String
    Dim startTime As Date, endTime As Date
    Dim totalWorkMinutes As Double
    Dim numBreaks As Integer, totalBreakMinutes As Double
    Dim newEndTime As Date
    Dim currentTime As Date
    Dim restPeriods As String
    Dim i As Integer
    
    ' Get input (replace with cell references like Range("A1").Value for worksheet integration)
    startInput = InputBox("Enter start time (e.g., 830, 9:15):")
    endInput = InputBox("Enter end time (e.g., 1030, 12:34):")
    
    ' Parse input times into valid dates
    startTime = ParseTime(startInput)
    endTime = ParseTime(endInput)
    
    ' Calculate total work minutes (handle cross-day cases where end time is next day)
    totalWorkMinutes = (endTime - startTime) * 1440
    If totalWorkMinutes < 0 Then totalWorkMinutes = totalWorkMinutes + 1440
    
    ' Validate input range
    If totalWorkMinutes <= 0 Then
        MsgBox "Invalid time range—end time must be after start time!"
        Exit Sub
    End If
    
    ' Calculate number of breaks and total break time
    numBreaks = Int((totalWorkMinutes - 1) / 45)
    totalBreakMinutes = numBreaks * 5
    newEndTime = endTime + totalBreakMinutes / 1440
    
    ' Generate rest period details
    restPeriods = ""
    currentTime = startTime
    
    For i = 1 To numBreaks
        ' Move to end of current work block
        currentTime = currentTime + 45 / 1440
        ' Record rest period start/end
        Dim restStart As Date, restEnd As Date
        restStart = currentTime
        restEnd = currentTime + 5 / 1440
        restPeriods = restPeriods & Format(restStart, "hh:mm") & "-" & Format(restEnd, "hh:mm") & ", "
        ' Update current time to end of rest period
        currentTime = restEnd
    Next i
    
    ' Clean up trailing comma
    If restPeriods <> "" Then restPeriods = Left(restPeriods, Len(restPeriods) - 2)
    
    ' Show results in message box
    MsgBox "Adjusted Work Schedule:" & vbCrLf & _
           "New End Time: " & Format(newEndTime, "hh:mm") & vbCrLf & _
           "Rest Periods: " & IIf(restPeriods = "", "None", restPeriods)
           
    ' Optional: Write results directly to worksheet cells
    ' Range("B1").Value = "New End Time: " & Format(newEndTime, "hh:mm")
    ' Range("B2").Value = "Rest Periods: " & IIf(restPeriods = "", "None", restPeriods)
End Sub

How to Use #

1. Run the CalculateWorkSchedule macro (assign it to a worksheet button for easier access if needed).
2. Enter your start and end times when prompted (supports all your listed formats, including invalid minutes like 67 which convert to valid time).
3. A message box will display the adjusted end time and all rest periods, or uncomment the last lines to write results directly to your worksheet.

Example Output #

For input start time 830 and end time 1030:

• New End Time: 10:40
• Rest Periods: 09:15-09:20, 10:05-10:10

Key Notes #

• Cross-day schedules (e.g., start 22:00, end 02:00) are handled automatically.
• Invalid minute values (like 67) are converted using Excel’s TimeSerial function (e.g., 23:67 becomes 00:07 the next day).

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