excel - 计算加类时间不一致的公式

Question

编辑:在评论 中实现文森特的解决方案后，它现在计算加类时间。

我的女朋友每天在家工作 6 个小时，并且可以自己安排时间。她让我为她制作一个时间表，她可以输入她的开始和结束时间，并自动计算她的小时数。它计算总工作时间没有问题(只要他们不超过午夜)，但我也想让它跟踪她的加类时间。

我有一个应该有效的公式，但问题是，如果给定日期的所有开始和结束时间都是上午或下午，那么无论总小时数超过 6 小时，它都不会计算加类时间。但是，只要您输入从 AM 时间开始到 PM 时间结束的时间值，例如 11:00 到 14:00，那么它就可以很好地计算当天的总加类时间。

这是时间表的屏幕截图，其中输入的时间总小时数超过 6，但没有从上午到下午的开始和结束时间:

这是一个示例，其中第一天输入的最后时间现在从上午时间变为下午时间。请注意，现在正在准确计算加类时间。

生成计算小时数的公式的 VBA 如下所示:

Sub calcHours(ByVal numDays As Integer)
    Dim newWeekRow As Integer: newWeekRow = 0

    With Sheets("timesheet")
        'Add the formula to calculate hours for the day
        Range(Cells(5, 17), Cells(numDays + 4, 17)).FormulaR1C1 = _
        "=SUM((RC[-2] - RC[-3]) + (RC[-4] - RC[-5]) + (RC[-6] - RC[-7]) + (RC[-8] - RC[-9]) + (RC[-10] - RC[-11]) + (RC[-12] - RC[-13]))"

        'Add formula to calculate overtime hours
        Range(Cells(5, 19), Cells(numDays + 4, 19)).FormulaR1C1 = _
        "=IF(RC[-2]-(6/24) > 6/24, RC[-2]-(6/24), 0)"

        'Add the formula to calculate hours for the week
        For ctr = 1 To numDays
            If (Cells(4 + ctr, 2).Value = "Saturday") Then 'found the end of the week
                If (newWeekRow = 0) Then 'end of the first week
                    Cells(4 + ctr, 18).FormulaR1C1 = "=SUM(R5C[-1]:RC[-1])"
                    newWeekRow = 4 + ctr
                Else
                    Cells(4 + ctr, 18).FormulaR1C1 = "=SUM(R[-6]C[-1]:RC[-1])"
                    newWeekRow = 4 + ctr
                End If
            End If

            If (ctr = numDays) Then 'reached the end of the last week
                Cells(4 + ctr, 18).FormulaR1C1 = "=SUM(R" & newWeekRow + 1 & "C[-1]:RC[-1])"
            End If
        Next ctr

        'Add formula to calulate total hours for the month
        With Cells(5 + numDays, 17)
            .FormulaR1C1 = "=SUM(R[-" & numDays & "]C:R[-1]C)"
            .Font.Bold = True
        End With
    End With
End Sub

Answer 1

目前，您对一周中的小时数求和的公式基本上是:

=SUM(E5-D5,G5-F5,I5-H5,K5-J5,M5-L5, O5-N5)

问题是当你过了午夜时，你无法表明时间是第二天。由于时间存储为小数，日期存储为整数，默认情况下，仅输入时间时，0 是当天的整数。您需要做的就是在他们外出时间小于进入时间时将时间加 1。有点作弊，并假设进出之间永远不会超过 24 小时。因此，考虑到这种作弊，并且在通过数学运算符时 bool True 和 False 分别转换为 1 和 0，您可以按如下方式调整公式:

=SUM(E5+(E5<D5)-D5,G5+(G5<F5)-F5,I5+(I5<H5)-H5,K5+(K5<J5)-J5,M5+(M5<L5)-L5, O5+(O5<N5)-N5)

或者，您可以记录他们工作当天的小时数，而不是在您过夜 sleep 的时间作为当天的分界线时被打破。即，如果您从星期一的 2300 工作到星期二的 0100，则将星期一记录为 2300 时间并超时为 23:59，而星期二记录时间为 00:00 和超时时间为 01:01。注意超时时间增加了 1 分钟。这是为了弥补 23:59 到 24:00 之间缺少的一分钟(这在技术上不存在于 excel 中，但适用于某些操作)

至于加类计算见文森特 G 的评论。该公式的另一个旋转可能是:

=MAX(Q5-6/24,0)

您将需要更改单元格引用以匹配您的 VBA 编程需求。这两个公式都更正了加类问题计算以及跨越午夜的开始和结束时间。