计算日期范围之外的时间 [英] Count time outside of range in a range of dates
问题描述
为了使一个新问题与之前的问题分开，我将其作为另一个问题。
In order keep a new question separate from previous I'm asking it as another question.
根据以下信息（全部在A列中），希望能够计算出某个时间（或不同时间）以外某个日期有多少个来电。例如，使用第一组数字在08/01/2019上从07:00到23:00之间有多少次呼叫，即：6:47
From the info below (all in column A), I'd like to be able to count how many calls come in on a date outside of a time (or between times). For example, how many calls on 08/01/2019 where NOT from 07:00 to 23:00 using the first set of numbers ie: 6:47
格式化我的源文件被弄乱了，但是下面的数字表示：
The formatting of my source file is messed up, but the numbers below represent:
2019年10月30日星期三11:42:11位置：一些。放置页面1
Wed Oct 30 11:42:11 2019 Location: Some. Place Page 1
Call Details by Date and Time
Call Date: 01/01/2019
____________________________________________________________________________________________________________________________________________________
Start Costed Call Call Access Dialed Number Call Cost Call Real Authorization Account Code Billing Code
Time Duration Origin Destination Code Charge Extension Code
_____ ________ __________ ___________ ______ ___________________________ __________ __________ __________ ________________ ____________ ____________
1:24 00:05:12 34320 34312 34312 $0.00 $0.00 34320
11:11 00:02:46 33314 34312 34312 $0.00 $0.00 33314
19:41 00:00:50 36424 34312 34312 $0.00 $0.00 36424
20:07 00:03:28 34227 34312 34312 $0.00 $0.00 34227
21:06 00:09:00 36335 34312 34312 $0.00 $0.00 36335
21:34 00:01:54 37641 34312 34312 $0.00 $0.00 37641
Wed Oct 30 11:42:11 2019 Location: Some. Place Page 2
Call Details by Date and Time
Call Date: 01/02/2019
____________________________________________________________________________________________________________________________________________________
Start Costed Call Call Access Dialed Number Call Cost Call Real Authorization Account Code Billing Code
Time Duration Origin Destination Code Charge Extension Code
_____ ________ __________ ___________ ______ ___________________________ __________ __________ __________ ________________ ____________ ____________
4:15 00:09:00 36335 34312 34312 $0.00 $0.00 36335
4:46 00:03:30 32970 34312 34312 $0.00 $0.00 32970
7:12 00:00:54 33022 34312 34312 $0.00 $0.00 33022
7:21 00:03:04 33655 34312 34312 $0.00 $0.00 33655
21:02 00:00:24 33277 34312 34312 $0.00 $0.00 33277
21:19 00:02:44 37606 34312 34312 $0.00 $0.00 37606
Wed Oct 30 11:42:11 2019 Location: Some. Place Page 3
Call Details by Date and Time
Call Date: 01/03/2019
____________________________________________________________________________________________________________________________________________________
Start Costed Call Call Access Dialed Number Call Cost Call Real Authorization Account Code Billing Code
Time Duration Origin Destination Code Charge Extension Code
_____ ________ __________ ___________ ______ ___________________________ __________ __________ __________ ________________ ____________ ____________
5:52 00:01:26 33322 34312 34312 $0.00 $0.00 33322
8:09 00:05:50 34229 34312 34312 $0.00 $0.00 34229
9:28 00:02:48 33952 34312 34312 $0.00 $0.00 33952
9月10日星期二08:52 ：40 2019位置：第4页
Tue Sep 10 08:52:40 2019 Location: Page 4
因此，此代码是一种用于计算日期中所有呼叫的方法（介于@中的呼叫日期和位置之间）
So this code is for a way to count all of the calls on a date (between "Call Date" and "Location" from @Plutian and it works well!
Sub counter()
Dim cel As Range
Dim i As Integer
Dim lastr As Integer
Dim calldate As String
i = 0
lastr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row 'determine last row of data
For Each cel In Sheet1.Range("A1:A" & lastr) 'start loop
If InStr(cel.Value, "Call Date") Then 'check if your value is "Call date" indicating start of data
If calldate = "" Then 'check if this is the first loop
calldate = cel.Value 'set calldate to the current loop.
Else 'if not first loop, write the current calldate + counter to the next available blank cell
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = calldate & " " & i
i = 0 'reste counter
calldate = cel.Value 'save next calldate value
End If
Else
If cel <> "" Then 'test if cell is blank, skip if it is
If InStr(cel, "Location") Then 'test if cell holds "Location, indicating it is not data. Skip if it is
Else
i = i + 1 'increase counter if part of data
End If
End If
End If
Next cel
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = calldate & " " & I & " calls" 'Write current data at end of loop.
End Sub
希望输出为：（下一个未使用的列）
Output hoped to be: (Next unused column)
通话日期：08/01/2019范围1以外的通话
Call Date: 08/01/2019 calls outside of range 1
通话日期：08/02/2019范围0以外的通话
Call Date: 08/02/2019 calls outside of range 0
通话日期：08/03/2019超出范围2的通话
Call Date: 08/03/2019 calls outside of range 2
谢谢！
推荐答案
由于这是我的代码开头，因此很容易适应您的需求。
As it is my code to begin with, it was easy to adapt to your needs.
我有什么在此处完成的操作添加了第二个计数器，每次找到匹配项时该计数器就会增加在指定时间之外。为了简化起见，我将两个结果串联在一起，所以您不必牺牲任何一个。
What I have done here is added a second counter that increases each time a hit is found outside the times specified. To make it easier I've concatenated the two results so you don't have to sacrifice either one.
Sub counter()
Dim cel As Range
Dim i As Integer, j As Integer
Dim lastr As Integer
Dim calldate As String
i = 0
lastr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row 'determine last row of data
For Each cel In Sheet1.Range("A1:A" & lastr) 'start loop
If InStr(cel.Value, "Call Date") Then 'check if your value is "Call date" indicating start of data
If calldate = "" Then 'check if this is the first loop
calldate = cel.Value 'set calldate to the current loop.
Else 'if not first loop, write the current calldate + both counters to the next available blank cell
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = calldate & " " & i & " calls, of which " & j & " outside hours"
i = 0 'reset counter
j = 0 'reset counter
calldate = cel.Value 'save next calldate value
End If
Else
If cel <> "" Then 'test if cell is blank, skip if it is
If IsDate(Left(cel.Value, 5)) Then 'test if first 5 characters of cell is a valid time.
If TimeValue(Left(cel.Value, 5)) < "07:00:00" Or TimeValue(Left(cel.Value, 5)) > "23:00:00" Then 'test if call is earlier than 07:00 or later than 23:00
j = j + 1 'increase counter if outside that time
End If
i = i + 1 'increase counter if part of data
End If
End If
End If
Next cel
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = calldate & " " & i & " calls, of which " & j & " outside hours" 'Write current data at end of loop.
End Sub
这篇关于计算日期范围之外的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
