计算间隔时间复杂

Question

我需要一些帮助来创建一个复杂的日期/时间计算。我有 2 列带有时间戳（日期加时间）。我需要找出差异，但这就是我需要公式做的事情：

• 计算两个时间戳之间的天/小时/分钟
• 如果已分配列为空，则保留单元格为空
• 考虑工作日和工作时间（不确定是否两者都可行）
• 以简单格式返回计算结果 - 天、小时、分钟。- 在理想情况下，如果返回的值不是 0，则仅显示每种类型（天/小时/分钟）。例如，一个单元格可能显示 03 分钟，另一个单元格可能显示 2 小时 18 分钟。而不是 0 天、0 小时、03 分钟等。

希望这是合理的，也是可行的。谷歌搜索会给出一大堆答案，但没有一个足够具体。谢谢！

Answer 1

我想建议两种方法，

1. 计算日、时、分（包括节假日）。

2. 计算除节假日外的天数、小时数和分钟数。

单元格中方法 1 的公式D241:

=IF(OR(ISBLANK(B241),ISBLANK(A241)),"No Data",INT(B241-A241)&" Days "&HOUR(MOD(B241-A241,1))&" Hour "&MINUTE(MOD(B241-A241,1))&" Minutes")

单元格中方法 2 的公式D248:

=IF(OR(ISBLANK(B248),ISBLANK(A248)),"No Data",NETWORKDAYS.INTL(A248,B248,1,$F$248:$F$250)&" days "&INT((B248-A248-INT(B248-A248))*24)&" hours "&INT((B248-A248-(INT((B248-A248-INT(B248-A248))*24)/24)-INT(B248-A248))*24*60)&" mins")

填写两个公式。

注意：

1. 为了社区的利益，我已将 方法 1，您和读者可以忽略它。
2. 调整节假日列表和单元格引用在公式中根据需要。
3. 您也可以更换没有数据错误消息空格处。

Answer 2

考虑到您的所有要求，我建议使用用户定义函数 (UDF)。

要输入此用户定义函数 (UDF)，alt-F11请打开 Visual Basic 编辑器。确保您的项目在 Project Explorer 窗口中突出显示。然后，从顶部菜单中选择插入/模块，并将下面的代码粘贴到打开的窗口中。

=elapsedWrkTime(A2,B2,Holidays)要使用此用户定义函数 (UDF)，请输入类似某些单元格的公式。

如果您研究代码，您应该能够弄清楚所使用的算法。

• 工作时间按上午 9 点至下午 5 点计算

• 除此之外的工作时间，或者周末或节假日的工作时间不计算在内。

• 一旦我们获得这些信息，就会创建并格式化天/小时/分钟的输出。

您需要对其进行测试并确认它提供了您想要的输出并准确计算。

Option Explicit
Function elapsedWrkTime(startDT As Date, endDt As Date, Holidays As Range) As Variant
    Const WORKING_DAY_START As Date = #9:00:00 AM#
    Const WORKING_DAY_END As Date = #5:00:00 PM#
    Dim adjTimeStart As Date, adjTimeEnd As Date, totTime As Date
    Dim D As Date

For D = DateValue(startDT) To DateValue(endDt)
    If Not isHoliday(D, Holidays) Then
    Select Case Weekday(D)
        Case 2 To 6 'Workday Mon-Fri
            'Adj for first and last days
            If D = DateValue(startDT) Then
                If TimeValue(startDT) <= WORKING_DAY_START Then
                    adjTimeStart = 0
                ElseIf TimeValue(startDT) >= WORKING_DAY_END Then
                    adjTimeStart = WORKING_DAY_START - WORKING_DAY_END
                Else
                    adjTimeStart = WORKING_DAY_START - TimeValue(startDT)
                End If
            End If

            If D = DateValue(endDt) Then
                If TimeValue(endDt) >= WORKING_DAY_END Then
                    adjTimeEnd = 0
                ElseIf TimeValue(endDt) <= WORKING_DAY_START Then
                    adjTimeEnd = WORKING_DAY_START - WORKING_DAY_END
                Else
                    adjTimeEnd = TimeValue(endDt) - WORKING_DAY_END
                End If
            End If

            totTime = totTime + WORKING_DAY_END - WORKING_DAY_START
    End Select
    End If
Next D

'elapsedWrkTime = totTime + adjTimeStart + adjTimeEnd


'Format the output as a text string


Dim dys As Long, hrs As Long, mns As Long
Dim wrkHrs As Double
Dim totWorkTime As Double

wrkHrs = (WORKING_DAY_END - WORKING_DAY_START) * 24
totWorkTime = (totTime + adjTimeStart + adjTimeEnd) * 24
dys = Int(totWorkTime / wrkHrs)

totWorkTime = totWorkTime - dys * wrkHrs
hrs = Int(totWorkTime)
mns = (totWorkTime - Int(totWorkTime)) * 60

Dim strOut As String
Select Case dys
    Case 1
        strOut = "1 day "
    Case Is > 1
        strOut = Format(dys, "0"" days """)
End Select
Select Case hrs
    Case 1
        strOut = strOut & "1 hour "
    Case Is > 1
        strOut = strOut & Format(hrs, "0"" hours """)
End Select
Select Case mns
    Case 1
        strOut = strOut & "1 minute"
    Case Is > 1
        strOut = strOut & Format(mns, "0"" minutes""")
End Select

If strOut = "" Then
    elapsedWrkTime = "No work hrs computed"
Else
    elapsedWrkTime = strOut
End If

End Function

Private Function isHoliday(dt As Date, Holidays As Range) As Boolean
    Dim v, w
v = Holidays
For Each w In v
    If Int(dt) = w Then
        isHoliday = True
        Exit Function
    End If
Next w
isHoliday = False
End Function