主题：[求助]处理时长计算

有两列时间，一列为【派单时间】格式：2018-01-01 12:02:02   另一列为【处理时限】格式：2018-01-01 12:02:02  

 

现在想根据条件通过【派单时间】来计算【处理时限】；命令该怎么写

 

1、如果【派单时间】中的时间大于16:00:00并且小于23:59:59；那么【处理时限】= 第二天12:00:00

 

2、如果【派单时间】中的时间大于00:00:00并且小于08:00:00；那么【处理时限】= 当天12:00:00

 

 

意思就是【派单时间】在16点以后到第二天8点以前的工单；【处理时限】都是第二天的中午12点

 

 

如何剔除非工作时间：比如工作时间为08:00-20:00、那么在计算时长时需要把非工作时间20:00-08:00剔除掉；公式该怎么写？

 

【归档时间】-【派单时间】=【处理时长】 要求剔除20:00-08:00这段时间

 

2018-07-06  10:00   -  2018-07-04 16:00 = 22 小时  （ 6号 = 10-8  ）+ （ 5号 = 20-8 ） + （ 4号 = 20-16 ） = 22小时

 

 

以下命令想去掉其中的  周末不计算时间  的设置（即不区分周末、周一至周日都按照08:00-20:00计算时长）；该去掉哪些命令？

 

 

Dim wtimes() As String = {"08:00-20:00"}
Dim stime As Date = e.Form.Controls("开始时间").Value
Dim etime As Date = e.Form.Controls("结束时间").Value
Dim sumHours As Double = 0
Dim sp As TimeSpan
Dim dhours As Double = 0

If stime.DayOfWeek <> 0 Then
    For Each wtime As String In wtimes
        Dim ary() As String = wtime.Split("-")
        Dim d1 As Date = stime.Date & " " & ary(0)
        Dim d2 As Date = stime.Date & " " & ary(1)
        sp = d2 - d1
        dhours += sp.TotalHours
        If stime < d1 Then
            If etime > d2 Then
                sp = d2 - d1
                sumhours += sp.TotalHours
            ElseIf etime > d1 Then
                sp = etime - d1
                sumhours += sp.TotalHours
            End If
        ElseIf stime < d2 Then
            If etime > d2 Then
                sp = d2 - stime
                sumhours += sp.TotalHours
            ElseIf etime > d1 Then
                sp = etime - stime
                sumhours += sp.TotalHours
            End If
        End If
    Next
End If

Dim d As Date = stime.AddDays(1)
Do While d.Date < etime.Date
    If d.DayOfWeek <> 0 Then
        sumhours += dhours
    End If
    d = d.AddDays(1)
Loop

If etime.DayOfWeek <> 0 AndAlso stime.Date < etime.Date Then
    For Each wtime As String In wtimes
        Dim ary() As String = wtime.Split("-")
        Dim d1 As Date = etime.Date & " " & ary(0)
        Dim d2 As Date = etime.Date & " " & ary(1)
        If etime > d2 Then
            sp = d2 - d1
            sumhours += sp.TotalHours
        ElseIf etime > d1 Then
            sp = etime - d1
            sumhours += sp.TotalHours
        End If
    Next
End If

msgbox("两日期相隔:" & sumhours & "小时")

 
此主题相关图片如下：360截图20180716221607270.jpg

 

Dim cmd As New SQLCommand
Dim dt As Date
cmd.C
cmd.CommandText = "Select GetDate()"
dt = cmd.ExecuteScalar()

 

For Each d As Row In Tables("装机工单盯控清单表").Rows
 
    If  ( d.Isnull("缓装状态") Or  d("缓装状态") = "待客户短信确认"  Or  d("缓装状态") = "客户已拒绝" Or  d("缓装状态") = "未进行缓装" ) And  d("工单号") <> ""   Then

 

Dim wtimes() As String = {"08:00-20:00"}
Dim stime As Date = dt
Dim etime As Date = d("达到服开时间")
Dim sumHours As Double = 0
Dim sp As TimeSpan
Dim dhours As Double = 0

    For Each wtime As String In wtimes
        Dim ary() As String = wtime.Split("-")
        Dim d1 As Date = stime.Date & " " & ary(0)
        Dim d2 As Date = stime.Date & " " & ary(1)
        sp = d2 - d1
        dhours += sp.TotalHours
        If stime < d1 Then
            If etime > d2 Then
                sp = d2 - d1
                sumhours += sp.TotalHours
            ElseIf etime > d1 Then
                sp = etime - d1
                sumhours += sp.TotalHours
            End If
        ElseIf stime < d2 Then
            If etime > d2 Then
                sp = d2 - stime
                sumhours += sp.TotalHours
            ElseIf etime > d1 Then
                sp = etime - stime
                sumhours += sp.TotalHours
            End If
        End If
    Next

Dim d As Date = stime.AddDays(1)
Do While d.Date < etime.Date

        sumhours += dhours

    d = d.AddDays(1)
Loop

If stime.Date < etime.Date Then
    For Each wtime As String In wtimes
        Dim ary() As String = wtime.Split("-")
        Dim d1 As Date = etime.Date & " " & ary(0)
        Dim d2 As Date = etime.Date & " " & ary(1)
        If etime > d2 Then
            sp = d2 - d1
            sumhours += sp.TotalHours
        ElseIf etime > d1 Then
            sp = etime - d1
            sumhours += sp.TotalHours
        End If
    Next
End If

 

d("工单时长") = sumhours

 

    Else
        If    d("缓装状态") = "客户已同意" And d.Isnull("客户原因缓装结束时间")  = Nothing  Then
            Dim tp As TimeSpan = dt - CDate(d("客户原因缓装结束时间"))
            d("工单时长") = Format(tp.TotalSeconds() /"3600","00.00")
        Else
            d("工单时长") = Nothing
        End If
    End If

根据9楼中【工单时长】要进行查询，查询按钮公式如下；提示错误。命令该怎么修改？

 

【工单时长】类型为“双精数小数”

 

If e.Form.Controls("起始时长").value  = "" Or e.Form.Controls("截止时长").value  = ""  Then 
    MessageBox.show("请选择要加载的【起始时长】【截止时长】!!!", "非常关键提醒")
Else
    DataTables("装机工单盯控清单表").LoadFIlter =  "工单时长 >= '" & e.Form.Controls("起始时长").value & "' and 工单时长 <= '" & e.Form.Controls("截止时长").value & "' "
    DataTables("装机工单盯控清单表").Load
   
    MessageBox.show("完成【装机工单盯控清单表】的 加载 工作!!!", "非常关键提醒")
End If

 

 

此主题相关图片如下：360截图20180805181707817.jpg