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

有两列时间，一列为【派单时间】格式：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点

 

 

Dim d As Date = "2018-05-21 16:31:25" 'e.DataRow("派单时间")
If d.Hour >= 16 Then
    d = d.AddDays(1)
    d = new Date(d.year, d.month, d.day, 12, 0, 0)
ElseIf d.hour < 8 Then
    d = new Date(d.year, d.month, d.day, 12, 0, 0)
End If
msgbox(d)

如何剔除非工作时间：比如工作时间为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小时

 

 

参考

 

http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=72686&skin=0

 

以下命令想去掉其中的  周末不计算时间  的设置（即不区分周末、周一至周日都按照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 & "小时")

If stime.DayOfWeek <> 0 Then 这个的意思是，不是周日。

 

不是周六、周日，这样写 If stime.DayOfWeek <> 6 AndAlso stime.DayOfWeek <> 0 Then

 
此主题相关图片如下：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

"达到服开时间"是日期型的列吗

你的d变量名重复定义

 

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

 

For Each r As Row In Tables("装机工单盯控清单表").Rows
   
    If  ( r.Isnull("缓装状态") Or  r("缓装状态") = "待客户短信确认"  Or  r("缓装状态") = "客户已拒绝" Or  r("缓装状态") = "未进行缓装" ) And  r("工单号") <> ""   Then
       
       
       
       
        Dim wtimes() As String = {"08:00-20:00"}
        Dim stime As Date = dt
        Dim etime As Date = r("达到服开时间")
        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
       
       
       
       
        r("工单时长") = sumhours
       
       
       
       
    Else
        If    r("缓装状态") = "客户已同意" And r.Isnull("客户原因缓装结束时间")  = Nothing  Then
            Dim tp As TimeSpan = dt - CDate(r("客户原因缓装结束时间"))
            r("工单时长") = Format(tp.TotalSeconds() /"3600","00.00")
        Else
            r("工单时长") = Nothing
        End If
    End If
Next

根据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