由于发布的公式准确地返回了DC_CREATION_DATE和ACTUAL_END_DATE之间的工作时间，问题似乎是如何使用VBA输入Excel公式。

• • 运算公式：**

= ( NETWORKDAYS.INTL( [@[DC_CREATION_DATE]], [@[ACTUAL_END_DATE]], "0000000" ) -1 ) * ( Upper - Lower )
 + IF( NETWORKDAYS.INTL( [@[ACTUAL_END_DATE]], [@[ACTUAL_END_DATE]], "0000000" ),
 MEDIAN( MOD( [@[ACTUAL_END_DATE]], 1 ), Upper, Lower ), Upper )
 - MEDIAN( NETWORKDAYS.INTL( [@[DC_CREATION_DATE]], [@[DC_CREATION_DATE]], "0000000" )
 * MOD( [@[DC_CREATION_DATE]], 1 ), Upper, Lower )

上述公式似乎是从Excel表（即ListObject）中获得的，如这些参数所示：而Upper和Lower似乎对应于Defined Names
使用标准单元格作为参数的相同公式如下所示：

= ( NETWORKDAYS.INTL( B7, C7, "0000000" ) -1 ) * ( Upper - Lower )
 + IF( NETWORKDAYS.INTL( C7, C7, "0000000" ),
 MEDIAN( MOD( C7, 1 ), Upper, Lower ), Upper )
 - MEDIAN( NETWORKDAYS.INTL( B7, B7, "0000000" )
 * MOD( B7, 1 ), Upper, Lower )

• 注意参数：[@[DC_CREATION_DATE]]和[@[ACTUAL_END_DATE]]分别替换为单元格B7和C7 *

这就是行动守则的问题所在

• • 不会替换所有参数 *
• 仅更换@[DC_CREATION_DATE]而非[@[DC_CREATION_DATE]]
• 仅更换@[ACTUAL_END_DATE]而非[@[ACTUAL_END_DATE]]
• 此外，还使用[和] Package 上部和下部

• • 使用VBA处理Excel公式：**

我建议在公式的开头添加DC_CREATION_DATE和ACTUAL_END_DATE的验证，如下所示：

= IF( [@[ACTUAL_END_DATE]] < [@[DC_CREATION_DATE]], 0,
 ( NETWORKDAYS.INTL( [@[DC_CREATION_DATE]], [@[ACTUAL_END_DATE]], "0000000" ) -1 ) * ( Upper - Lower )
 + IF( NETWORKDAYS.INTL( [@[ACTUAL_END_DATE]], [@[ACTUAL_END_DATE]], "0000000" ),
 MEDIAN( MOD( [@[ACTUAL_END_DATE]], 1 ), Upper, Lower ), Upper )
 - MEDIAN( NETWORKDAYS.INTL( [@[DC_CREATION_DATE]], [@[DC_CREATION_DATE]], "0000000" )
 * MOD( [@[DC_CREATION_DATE]], 1 ), Upper, Lower ) )

我建议使用VBA处理excel公式的方法如下：
1.将公式中的参数替换为关键字，在运行过程时，这些关键字将被实际值的R1C1引用所替换：
...

= IF( #END < #INI, 0," & vbLf & _
 ( NETWORKDAYS.INTL( #INI, #END, "0000000" ) -1 ) * ( #UPR - #LWR )" & vbLf & _
 + IF( NETWORKDAYS.INTL( #END, #END, "0000000" )," & vbLf & _
 MEDIAN( MOD( #END, 1 ), #UPR, #LWR ), #UPR )" & vbLf & _
 - MEDIAN( NETWORKDAYS.INTL( #INI, #INI, "0000000" )" & vbLf & _
 * MOD( #INI, 1 ), #UPR, #LWR ) )"

其中：
#INI = [@[DC_CREATION_DATE]]
#END = [@[ACTUAL_END_DATE]]
#LWR = Lower
#UPR = Upper

By using the R1C1 reference of the cells we can update the formulas for the entire range at once instead of looping over each cell.

1.定义一个常量以保存公式模板：
...

Const kFmlHours As String = "= IF( #END < #INI, 0," & vbLf & _
    " ( NETWORKDAYS.INTL( #INI, #END, ""0000000"" ) -1 ) * ( #UPR - #LWR )" & vbLf & _
    " + IF( NETWORKDAYS.INTL( #END, #END, ""0000000"" )," & vbLf & _
    " MEDIAN( MOD( #END, 1 ), #UPR, #LWR ), #UPR )" & vbLf & _
    " - MEDIAN( NETWORKDAYS.INTL( #INI, #INI, ""0000000"" )" & vbLf & _
    " * MOD( #INI, 1 ), #UPR, #LWR ) )"

1.根据需要为参数定义变量：
...

Dim sFmlHours As String
Dim TimeLwr As Double, TimeUpr As Double
Dim sDateIni As String, sDateEnd As String

1.将公式模板中的关键字替换为相应的值或R1C1引用：
...

With .Range("V2")
            sDateIni = Range("N2").Address(0, 1, xlR1C1, False, .Cells)
            sDateEnd = Range("R2").Address(0, 1, xlR1C1, False, .Cells)
            sFmlHours = kFmlHours
            sFmlHours = Replace(sFmlHours, "#INI", sDateIni)
            sFmlHours = Replace(sFmlHours, "#END", sDateEnd)
            sFmlHours = Replace(sFmlHours, "#LWR", TimeLwr)
            sFmlHours = Replace(sFmlHours, "#UPR", TimeUpr)
        End With

1.输入整个范围的公式，（也可以用结果值替换公式）：
...

With .Range("V2:V" & lRow)
            .FormulaR1C1 = sFmlHours    'Enter formula
            .Value = .Value             'Replace Formula with Value
        End With

• • 程序：**

本程序仅包括工作时间的计算：

Sub Formula_Working_Hours()
   
Const kFmlHours As String = "= IF( #END < #INI, 0," & vbLf & _
    " ( NETWORKDAYS.INTL( #INI, #END, ""0000000"" ) -1 ) * ( #UPR - #LWR )" & vbLf & _
    " + IF( NETWORKDAYS.INTL( #END, #END, ""0000000"" )," & vbLf & _
    " MEDIAN( MOD( #END, 1 ), #UPR, #LWR ), #UPR )" & vbLf & _
    " - MEDIAN( NETWORKDAYS.INTL( #INI, #INI, ""0000000"" )" & vbLf & _
    " * MOD( #INI, 1 ), #UPR, #LWR ) )"

Dim wsMain As Worksheet, wsPrcs As Worksheet
Dim sFmlHours As String
Dim TimeLwr As Double, TimeUpr As Double
Dim sDateIni As String, sDateEnd As String
Dim lRow As Long
    
    Rem Set Lower & Upper Time
    TimeLwr = TimeSerial(8, 0, 0)
    TimeUpr = TimeSerial(23, 0, 0)
    
    With ThisWorkbook
        Set wsMain = .Sheets("RAW DATA")
        Set wsPrcs = .Sheets("Processed Data")
    End With
    
    lRow = wsMain.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
    
    With wsPrcs
                
        .Activate
        If Not (.AutoFilter Is Nothing) Then .AutoFilter.Range.AutoFilter
            
        Rem Set Formula
        With .Range("V2")
            sDateIni = Range("N2").Address(0, 1, xlR1C1, False, .Cells)
            sDateEnd = Range("R2").Address(0, 1, xlR1C1, False, .Cells)
            sFmlHours = kFmlHours
            sFmlHours = Replace(sFmlHours, "#INI", sDateIni)
            sFmlHours = Replace(sFmlHours, "#END", sDateEnd)
            sFmlHours = Replace(sFmlHours, "#LWR", TimeLwr)
            sFmlHours = Replace(sFmlHours, "#UPR", TimeUpr)
        End With
        
        Rem Enter Formula
        With .Range("V2:V" & lRow)
            .FormulaR1C1 = sFmlHours    'Enter formula
            .Value = .Value             'Replace Formula with Value
        End With
        
    End With

End Sub

这里有一个潜在的缺陷：

For Each i In Range("N2:N" & LastRow).SpecialCells(xlCellTypeVisible)
        StartDate = Range("N" & k).Value
        EndDate = Range("R" & k).Value
        Debug.Print (StartDate)
        Debug.Print (EndDate)
        Range("U" & k).Value = DateDiff("s", Range("N" & k).Value, Range("R" & k).Value)
        Range("V" & k).Value = "=(NETWORKDAYS.INTL([" & StartDate & "],[" & EndDate & "],""0000000"")-1)*([" & upper & "]- [" & lower & "])" _
                                    & "+IF(NETWORKDAYS.INTL([" & EndDate & "],[" & EndDate & "],""0000000""),MEDIAN(MOD([" & EndDate & "],1),[" & upper & "],[" & lower & "]),[" & upper & "])" _
                                    & "-MEDIAN(NETWORKDAYS.INTL([" & StartDate & "],[" & StartDate & "],""0000000"")*MOD([" & StartDate & "],1),[" & upper & "],[" & lower & "])"
        k = k + 1
Next i

您正在循环N列中的可见单元格，所以我假设这里应用了一些过滤器，并且隐藏了一些行。
如果第一行（#2）是隐藏的，那么你可以从i=N3开始，但是你的k值仍然是2，所以你阅读/写的不是你想要的行。
在循环中，i.EntireRow将为您提供每个可见行，因此您可以使用（eg）

Dim rw As Range
'....
For Each i In Range("N2:N" & LastRow).SpecialCells(xlCellTypeVisible)
    Set rw = i.EntireRow
    StartDate = rw.Columns("N").Value 'or just i.Value...
    EndDate = rw.Columns("R").Value
    'etc etc

迟来的回复，不过看看吧。

Sub RAWDATA_SORT()
    
    Dim Main As Worksheet, Processed As Worksheet
    Dim LastRow As Long, col As Long, k As Integer
    Dim colName As String, maincolName As String
    Dim i As Long
    Dim Headers As Range, SearchHeaders As Range
    Dim upper As Date, lower As Date, StartDate As Date, EndDate As Date
    
    Dim vR(), vTime()
    
    
    'On Error Resume Next
    Set Main = Sheets("RAW DATA")
    'Main.Name = "RAW DATA"
    'Sheets.Add(After:=Sheets("RAW DATA")).Name = "Processed Data"
    Set Processed = Sheets("Processed Data")
    'Main.Activate
    If Main.FilterMode Then
        Main.ShowAllData
    End If
    Set Headers = Main.Range("1:1")
    LastRow = 0
    'lower = Format(TimeValue("08:00 AM"), "hh:mm AMPM")
    'upper = Format(TimeValue("11:00 PM"), "hh:mm AMPM")
    'Debug.Print (lower)
    'Debug.Print (upper)
    
    ' More Code Here
    
    With Processed
       ' .Activate
        .AutoFilterMode = False
        If .FilterMode Then
            .ShowAllData
        End If
    End With
    ' More Code Here
    'LastRow = Main.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
    LastRow = Main.Range("n" & Rows.Count).End(xlUp).Row
    
    ReDim vR(1 To LastRow, 1 To 1)
    ReDim vTime(1 To LastRow, 1 To 2)
    
    Dim rngDB As Range, vDB
   
    Set rngDB = Main.Range("n2", "R" & LastRow)
    vDB = rngDB
    For i = 1 To UBound(vDB, 1)
        vTime(i, 2) = DayWorkTime(vDB(i, 1), vDB(i, 5))
        vTime(i, 1) = vTime(i, 2) * 24 * 3600
    Next i
        
    With Processed
        .Range("U2").Resize(UBound(vR), 2) = vTime
        .Range("u:u").NumberFormat = "#,##0"
        .Range("v:v").NumberFormat = "[H]:mm"
    End With
    

End Sub

Function DayWorkTime(stime, etime)
    Dim Start As Date, EndTime As Date
    Dim vTime()
    Dim i As Long, k As Integer
    Dim n As Integer
    
    Application.Volatile (0)
 
    If stime > etime Then
        etime = etime + 1
    End If
    k = Int(etime) - Int(stime)
    
    For i = 0 To k
        n = n + 1
        ReDim Preserve vTime(1 To 2, 1 To n)
        If i = 0 Then
            vTime(1, n) = stime - Int(stime)
            vTime(2, n) = 1
        ElseIf k >= 1 Then
            If i = k Then
                vTime(1, n) = 0
                vTime(2, n) = etime - Int(etime)
            Else
                vTime(1, n) = 0
                vTime(2, n) = 1
            End If
        End If
    Next i
        
    For i = 1 To n
        DayWorkTime = DayWorkTime + DayWork(vTime(1, i), vTime(2, i))
    Next i
End Function
Function DayWork(stime, etime)
    Dim DaySt, DayEt
    Dim Start As Date, EndTime As Date
    
    Application.Volatile (0)
    DaySt = TimeSerial(8, 0, 0)
    DayEt = TimeSerial(23, 0, 0)
    With WorksheetFunction
        Start = .Max(stime, DaySt)
        EndTime = .Min(etime, DayEt)
    End With
    If Start > EndTime Then Exit Function
    DayWork = EndTime - Start
End Function

我只是简单地使用excel公式完成了这一过程，并且使用我填充的样本数据工作得很好
Sample data and Result
单元格C2中使用的公式

=(NETWORKDAYS.INTL(A2,B2,"0000000")-2)*15
+IF(TIME(23,0,0)-TIME(HOUR(A2),MINUTE(A2),0)>=TIME(15,0,0),TIME(15,0,0),IF(TIME(HOUR(A2),MINUTE(A2),0)<TIME(23,0,0),TIME(23,0,0)-TIME(HOUR(A2),MINUTE(A2),0),0))*24
+IF(TIME(HOUR(B2),MINUTE(B2),0)-TIME(8,0,0)>=TIME(15,0,0),TIME(15,0,0),IF(TIME(HOUR(B2),MINUTE(B2),0)>TIME(8,0,0),TIME(HOUR(B2),MINUTE(B2),0)-TIME(8,0,0),0))*24

单元格D2中使用的公式

=ROUNDDOWN(C2/15,0)&" Days "&ROUNDDOWN(MOD(C2,15),0)&" Hours "& MOD(C2,1)*60 & " Minutes"

我正在尝试做的是转换工作持续时间，不包括开始和完成日期，将它们乘以15小时。对于开始日期和完成日期中的工作小时数，我正在检查它是否在08：00和23：00之间。以及工作小时数。
在得到总数之后，我再次将它们从总小时数转换为天数、小时数和分钟数，方法是除以15表示天数，除以余数表示小时数和分钟数