Excel时间论坛

Question

我试图找出时间表上的进出时间之间的差异来计算工作时间。

我有以下数据：

----A----------B-----------C----------D-------
----Time In----Time Out----Time In----Time Out
1---11:15 AM---12:05 PM----1:05 PM----1:10 PM
2---1:05 PM----1:20 PM-----2:20 PM----4:00 PM
3---11:35 PM---12:05 AM----1:05 AM----1:30 AM
4---1:20 PM----2:20 PM------------------------

这些单元格全部格式化为时间 (HH:MM AM/PM)

我使用以下公式来计算工作时间：

=(SUM(B1-A1)*24)+(SUM(D1-C1)*24)

该公式产生以下结果：

----A----------B-----------C----------D-----------E----------
----Time In----Time Out----Time In----Time Out----TOTAL HOURS
1---11:15 AM---12:05 PM----1:05 PM----1:10 PM-----0.92-------
2---1:05 PM----1:20 PM-----2:20 PM----4:00 PM-----1.92-------
3---11:35 PM---12:05 AM----1:05 AM----1:30 AM-----(23.08)----
4---1:20 PM----2:20 PM----------------------------1.00-------

第 1、2 行和第 2 行第 4 行显示正确，但第 3 行显示错误。它应该是 0.92 而不是 -23.08。我知道发生这种情况是因为公式无法判断 B 列（上午 12:05）是在第二天。

不幸的是，添加日期不是一个选项……只能添加时间。可以编写一个公式来解决这个限制吗？

Answer 1

将结果数模 24，它应该始终为您提供正数。

你的等式变成：

=MOD(SUM((B1-A1)*24),24) + MOD((SUM(D1-C1)*24),24) 

Answer 2

如果结果小于零，则加 24。

Answer 3

我正在为那些可能想知道的人添加一个 VBA 答案，因为这是一个很好的问题，也是其他人可能遇到的问题。 VBA 更灵活，因为您可以使用不同的时间格式，但它仍然可以工作（例如，您可以在时间中包含实际日期）。这利用了 duffymo 的解决方案。

Function HoursWorked(t1 As Date, t2 As Date, _
                     t3 As Date, t4 As Date) As Double

Application.ScreenUpdating = False
Dim shift1 As Double
Dim shift2 As Double

shift1 = DateDiff("n", t1, t2) / 60
If shift1 < 0 Then
    shift1 = shift1 + 24
End If

shift2 = DateDiff("n", t3, t4) / 60
If shift2 < 0 Then
    shift2 = shift2 + 24
End If

HoursWorked = shift1 + shift2
Application.ScreenUpdating = True

End Function

请注意，如果班次 3 和班次 4 为空，该功能仍将正常运行。另请注意，我使用秒然后除以 60 以确保精度不会丢失（如果在 DateDiff 函数中使用“h”而不是“n”，则会丢失精度）。

如果您只想获取两个日期/时间之间的小时数，您可以使用以下命令：

Function HoursDiff(ByVal date1 As Date, _
                   ByVal date2 As Date) As Double

Application.ScreenUpdating = False
Dim result As Double

result = DateDiff("n", date1, date2) / 60
If result < 0 Then
    result = result + 24
End If

HoursDiff = result
Application.ScreenUpdating = True

End Function