Foxtable(狐表)用户栏目专家坐堂 → [求助]处理时长计算


  共有2949人关注过本帖树形打印复制链接

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

帅哥哟,离线,有人找我吗?
wumingrong1
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:五尾狐 帖子:1029 积分:6497 威望:0 精华:0 注册:2014/1/3 12:49:00
[求助]处理时长计算  发帖心情 Post By:2018/5/21 10:19:00 [只看该作者]

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

 

 


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/5/21 10:31:00 [只看该作者]

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)


 回到顶部
帅哥哟,离线,有人找我吗?
wumingrong1
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:五尾狐 帖子:1029 积分:6497 威望:0 精华:0 注册:2014/1/3 12:49:00
  发帖心情 Post By:2018/7/10 14:16:00 [只看该作者]

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

 

 

[此贴子已经被作者于2018/7/10 14:22:57编辑过]

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/7/10 14:36:00 [只看该作者]


 回到顶部
帅哥哟,离线,有人找我吗?
wumingrong1
  5楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:五尾狐 帖子:1029 积分:6497 威望:0 精华:0 注册:2014/1/3 12:49:00
  发帖心情 Post By:2018/7/12 23:06:00 [只看该作者]

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


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  6楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/7/12 23:14:00 [只看该作者]

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

 

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


 回到顶部
帅哥哟,离线,有人找我吗?
wumingrong1
  7楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:五尾狐 帖子:1029 积分:6497 威望:0 精华:0 注册:2014/1/3 12:49:00
  发帖心情 Post By:2018/7/16 22:17:00 [只看该作者]

 
图片点击可在新窗口打开查看此主题相关图片如下: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



 回到顶部
帅哥,在线噢!
有点蓝
  8楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105473 积分:536350 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2018/7/16 22:23:00 [只看该作者]

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

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  9楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/7/17 9:25:00 [只看该作者]

你的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


 回到顶部
帅哥哟,离线,有人找我吗?
wumingrong1
  10楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:五尾狐 帖子:1029 积分:6497 威望:0 精华:0 注册:2014/1/3 12:49:00
  发帖心情 Post By:2018/8/5 18:14:00 [只看该作者]

根据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
图片点击可在新窗口打开查看
[此贴子已经被作者于2018/8/5 18:17:37编辑过]

 回到顶部
总数 11 1 2 下一页