|
也试了一下,看是否你需要的。
如果时间没跨天,则此题好解决,如从上午9点至24点,但此题有个例外情况必须考虑,即从晚上18点到次日2点,如果只单纯考虑起始小时数的话,则出错。因for 18 to 2,不进入循环体内部,则此人上网的所有时数均漏统计了。
Sub tt()
Dim arr(0 To 23), ar3
Set wk = Worksheets(1)
n = wk.Range("D65536").End(3).Row
ReDim ar3(1 To n - 1)
ar1 = wk.Range("D2:D" & n): ar2 = wk.Range("E2:E" & n)
For i = 1 To n - 1
s1 = Year(ar1(i, 1)): s2 = Month(ar1(i, 1)): s3 = Day(ar1(i, 1)): s4 = Hour(ar1(i, 1))
t1 = CDate(s1 & "/" & s2 & "/" & s3)
t2 = DateAdd("h", s4, t1)
t3 = ar2(i, 1)
时数 = DateDiff("h", t2, t3)
ar3(i) = 时数 + 1
p = s4
For j = 1 To 时数 + 1
arr(p) = arr(p) + 1
If p = 23 Then
p = 0
Else
p = p + 1
End If
Next
Next
wk.[T2:T25].ClearContents
wk.Range("T2").Resize(24, 1) = WorksheetFunction.Transpose(arr)
End Sub |
|