excel - 试图获得 2 个日期之间的差异（3 个不同的单元格）

Question

你能帮我获得两个日期之间的差异吗（仅限工作时间，这非常重要）

看看这张图片：

首次响应的计算方法是：首次响应日期和问题日期之间的差值

已用时间由以下差值计算得出：上次响应日期和问题日期

到目前为止，这是我的宏（它不能正常工作）：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WORKING_DAY_START As String = "09:00"
Const WORKING_DAY_END As String = "18:00"
Const FORMULA_WORKING_TIME As String = _
    "=(INT(E2-D2)*(""" & WORKING_DAY_END & """-""" & WORKING_DAY_START & """)" & _
    "+MEDIAN(MOD(E2,1),""" & WORKING_DAY_END & """,""" & WORKING_DAY_START & """)" & _
    "-MEDIAN(MOD(D2,1),""" & WORKING_DAY_END & """,""" & WORKING_DAY_START & """))"
Const FORMULA_ELAPSED_TIME As String = "=F2-D2"
Dim lastrow As Long

    On Error GoTo ws_bdc_exit

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    With Me

        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'input Elapsed Time
        .Range("H2").Resize(lastrow - 1).Formula = FORMULA_ELAPSED_TIME

        'input First Response time
        .Range("G2").Resize(lastrow - 1).Formula = FORMULA_WORKING_TIME

        With .Range("G2:H2").Resize(lastrow - 1)
            .Value = .Value
            .NumberFormat = "##0.00"
        End With
    End With

ws_bdc_exit:
    Target.Offset(1).Select

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

编辑＃1：我应该从周一到周五获得工作时间（不包括周末，但我不知道该怎么做）

编辑＃2：差异应以小时显示

编辑#3：之前，我正在使用这个宏（一切正常，但我没有得到工作时间）

Public cVal
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
t1 = TimeValue(CStr(Cells(i, "D").Value))
t2 = TimeValue(CStr(Cells(i, "E").Value))
t3 = TimeValue(CStr(Cells(i, "F").Value))

'input First Response time
If Hour(t2) - Hour(t1) = 0 Then
    Cells(i, "G").Value = Round((Minute(t2) - Minute(t1)) / 60, 2)
Else
    Cells(i, "G").Value = Hour(t2) - Hour(t1) + Round((Minute(t2) - Minute(t1)) / 60, 2)
End If

'input Elapsed Time
If Hour(t3) - Hour(t1) = 0 Then
    Cells(i, "H").Value = Round((Minute(t3) - Minute(t1)) / 60, 2) '- Cells(i, "J").Value - Cells(i, "J").Value
Else
    Cells(i, "H").Value = Hour(t3) - Hour(t1) + Round((Minute(t3) - Minute(t1)) / 60, 2) '- Cells(i, "J").Value
End If
Next i

Target.Offset(1).Select
End Sub

标签： excelvba

Answer 1

我写了一个函数，它应该只计算周一至周五的工作时间。

请注意，在您发布的示例中，某些日期是周六/周日，因此将计算为零。

算法：

• 将周一至周五的每一天的工作时间计算为WORKING_DAY_START -WORKING_DAY_END` 小时。
• 如果这一天恰好是计算的第一天或最后一天，请进行调整。

您可以在工作表本身上使用此函数，也可以从仅使用值填充单元格的宏中调用 if。

下面我将展示你的原始数据，以及一些改变你周末工作日期的额外行。

Option Explicit
Function elapsedWorkTime(startDT As Date, endDt As Date) As Date
    Const WORKING_DAY_START As Date = #9:00:00 AM#
    Const WORKING_DAY_END As Date = #6:00:00 PM#
    Dim adjTimeStart As Date, adjTimeEnd As Date, totTime As Date
    Dim D As Date

For D = DateValue(startDT) To DateValue(endDt)
    Select Case Weekday(D)
        Case 2 To 6
            '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
Next D

elapsedWorkTime = totTime + adjTimeStart + adjTimeEnd

End Function

编辑更正了屏幕截图的格式

请注意，由于您希望将输出表示为小时，因此工作表单元格中的公式类似于：

=elapsedWorkTime(C2;D2)*24

注意 的差异5541。在您的示例中，您显示了8,52经过时间的值。但是在您编写的需求声明中，您只想包括工作时间。工作时间结束，18:00因此在此之后花费的时间不应计算在内。