基于Excel VBA统计指定日期内非目标时段的通话数量需求

Got it, let's fix this up for you. Below is a modified VBA script that'll scan your messy call log in Column A, count calls falling outside the 07:00-23:00 window per date, and output results to the first blank column in your worksheet—matching the format you need.

Modified VBA Code #

Sub CountCallsOutsideRange()
    Dim cel As Range
    Dim i As Integer
    Dim lastr As Integer
    Dim calldate As String
    Dim outputCol As Integer
    Dim callTime As String
    Dim timeVal As Date
    Dim colonPos As Integer
    Dim hourPart As String, minPart As String
    
    i = 0
    lastr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' Get last row of data
    ' Auto-find first blank column for output
    outputCol = Sheet1.Cells(1, Sheet1.Columns.Count).End(xlToLeft).Offset(0, 1).Column
    
    For Each cel In Sheet1.Range("A1:A" & lastr)
        If InStr(cel.Value, "Call Date") Then
            ' Output count for previous date if we're switching to a new one
            If calldate <> "" Then
                Sheet1.Cells(Rows.Count, outputCol).End(xlUp).Offset(1, 0).Value = calldate & " calls outside of range " & i
                i = 0 ' Reset counter for new date
            End If
            calldate = cel.Value ' Store current date header
        Else
            ' Skip blank cells and "Location" rows
            If cel.Value <> "" And InStr(cel.Value, "Location") = 0 Then
                ' Extract HH:MM time from call record (adjust if your format differs)
                colonPos = InStr(cel.Value, ":")
                If colonPos > 1 Then
                    ' Pull hour and minute parts around the colon
                    hourPart = Mid(cel.Value, colonPos - 2, 2)
                    minPart = Mid(cel.Value, colonPos + 1, 2)
                    
                    ' Make sure extracted parts are valid numbers
                    If IsNumeric(hourPart) And IsNumeric(minPart) Then
                        callTime = hourPart & ":" & minPart
                        timeVal = TimeValue(callTime)
                        
                        ' Check if time is outside 7 AM to 11 PM
                        If timeVal < TimeValue("07:00") Or timeVal >= TimeValue("23:00") Then
                            i = i + 1
                        End If
                    End If
                End If
            End If
        End If
    Next cel
    
    ' Output count for the last date after loop finishes
    If calldate <> "" Then
        Sheet1.Cells(Rows.Count, outputCol).End(xlUp).Offset(1, 0).Value = calldate & " calls outside of range " & i
    End If
End Sub

Key Changes & Explanations #

• Dynamic Output Column: No more hardcoding columns—this script automatically finds the first blank column, so it won't overwrite existing data.
• Time Handling: It extracts the HH:MM time from each call record, validates that the hour/minute are numeric, then converts it to a time value for accurate comparison.
• Range Check: Uses TimeValue() to compare against your 07:00-23:00 window. Calls before 7 AM or at/after 11 PM get counted.
• Streamlined Date Tracking: The logic for switching between dates and resetting counters is cleaned up to avoid leftover values from previous iterations.

Quick Notes #

• If your call logs use 12-hour time (with AM/PM), you'll need to tweak the time extraction to convert it to 24-hour format first. For example, add checks for "PM" to add 12 to the hour value.
• Always test this on a copy of your data first to make sure it correctly pulls times from your specific log format.

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