vba - 时差以小时间隔分割excel vba

Question

这是一位思想家!

我有以下数据集:

Data http://im61.gulfup.com/AkqnzH.png

我的目标是获取时差并将其分成小时间隔。即每个人每小时花费多少时间，如下:

Table http://im45.gulfup.com/UupkLe.png

所以最终结果应该是这样的: Result http://im44.gulfup.com/WNl5Z6.png

涵盖第一个场景非常简单:

Private Sub CommandButton21_Click()

LastRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row

For MyRow = 2 To LastRow

If Format(Worksheets("Sheet1").Range("B" & MyRow).Value, "hh") = Format(Worksheets("Sheet1").Range("C" & MyRow).Value, "hh") Then

    Set oLookin = Worksheets("Sheet2").UsedRange
    sLookFor = Worksheets("Sheet1").Range("A" & MyRow)
    Set oFound = oLookin.Find(What:=sLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

    If Not oFound Is Nothing Then
        Worksheets("Sheet2").Cells(oFound.Row, (Format(Worksheets("Sheet1").Range("B" & MyRow).Value, "hh")) + 1).Value = _
        Worksheets("Sheet1").Range("C" & MyRow).Value - Worksheets("Sheet1").Range("B" & MyRow).Value
    End If
End If

Next MyRow

End Sub

然而，涵盖第二个和第三个场景是我发现具有挑战性的地方。如果“进入时间”与“超时”时间不同，我该怎么办？然后我如何分割时间并在 00:00:00 停止，因为从 17:00:09 减去 00:10:00 将得到 #############。

我一直在试图找出解决方案，甚至想过以某种方式使用类模块？但一直没能弄清楚:(

非常感谢您的帮助和建议。

Answer 1

当一个简单的公式就可以解决问题时，为什么还要花 50 行 VBA 代码呢？

=IF($C4>$B4,IF($B4<=D$1,IF($C4>=D$2,TIME(1,0,0),IF($C4<=D$1,"",$C4-D$1)),IF($B4>=D$2,"",IF($C4>=D$2,D$2-$B4,$C4-$B4))),IF($B4<=D$1,TIME(1,0,0),IF($B4>=D$2,"",D$2-$B4)))

向下和向右复制公式，并将数字格式更改为时间。

请注意两个辅助行 1 和 2，其中包含每个小时间隔的开始时间和结束时间。在第 3 行中，我使用以下公式重新构建了标题:

=TEXT(D1,"hh:mm")&"-"&TEXT(D2,"hh:mm")

如果您不喜欢这些辅助行，原则上您可以删除它们并使用 TIMEVALUE 从标题文本中提取时间值。和SEARCH功能。为此，请替换 D$1 的每个实例。在上面的第一个公式中

TIMEVALUE(LEFT(D3,SEARCH("-",D$3)))

以及 D$1 的每个实例与

=TIMEVALUE(MID(D3,SEARCH("-",D3)+1,50))

但在我看来，这样做有点可笑。

请注意，此公式无法处理超出 23:00-00:00 的时间列，即第二天。但它可以很容易地扩展来实现这一点，这留给读者作为练习。