EXCEL 时间函数难题 就高人指点

yjh_27

时间段矢量运算_自定义函数V1.33.rar

yygpdkkk

yjh_27 发表于 2013-4-12 23:04
时间段矢量运算_自定义函数V1.3.rar

1.就楼主本题来说，搞复杂了
2.要借助辅助单元格配合你的代码才能算出结果？
3.他人真要用你的这个函数，至少得花好几个小时先理解你的代码，然后才能使用（估计没几人有那耐心或能力去读懂你的代码，故最终谈不上使用你的函数）

joforn

yygpdkkk 发表于 2013-4-12 22:59
还是有问题！
1.正常的日期误判为“错误的日期输入”，显然代码存在逻辑错误
2.下图工作表第10行的结果 ...

多谢你的测试，那个错误提示是因为一个提前一个推后(为了少计算天数。)所致，重新转换成了全部推迟计算，应该不会再提示错误了。19楼的代码和测试文档已经全部更新，有空的话再帮我测试下吧。

yjh_27

1 只新编了TimeWork，其他原有
2 辅助单元格用于存储工作时间，便于修改
3 好用就行，不懂代码没关系。

只需看 工作时间表即可。

其他参见
时间段组的矢量运算,自定义函数

yygpdkkk

回复23楼：经粗粗测试，与我的代码结果一致
回复24楼：知道你的代码有一定的灵活性（初步理解应该是灵活在时间段和节假日及调休日可直接在工作表界面设置），但对于使用者来说，arrTime2参数的设置既麻烦也一时难以理解（arrTime2参数既便一年中只要在表格中输入一次，要输那么多的“＋”或“－”看起来也很怪异）。此外，若把你的函数用于代码中调用，其很难胜任。

yjh_27

arrTime2参数的设置好理解：

    开始时间                         结束时间            星期几工作      注释
2013/1/1 8:00:00       2013/5/31 12:00:00       1,2,3,4,5         2013/1/1至2013/5/31     周一至周五  8时至12时  工作
2013/6/1 9:00:00       2013/12/31 12:00:00      2,6                2013/6/1至2013/12/31   周二、周六  9时至12时  工作
2013/4/7 8:00:00       2013/4/7 12:00:00         +                   2013/4/7                                         8时至12时  休息调工作
2013/4/4                   2013/4/7                       -                    2013/4/4 0:00:00至2013/4/6 24:00:00                工作调休息（放假）

星期几工作：用“，”隔开即可；0或空表示每天都工作。

可放在作息时间表中，与计算表分离，看着就整齐了。

yygpdkkk

因3楼判断是否工作日的代码在速度上大大逊色于joforn的写法，故对3楼代码作相应修改，也改为Select结构，直接列示节假日。此外，直接列示的节假日采用日期文字亦可增加语言上的可移植性。

1. 
   
2. 
   
3. Function MyTime(theStartDate As Variant, theEndDate As Variant) As Variant
   
4.     Dim theDate1 As Date, theDate2 As Date, theTime1 As Date, theTime2 As Date, i&, n&
   
5.     MyTime = 0 '确保一次赋值
   
6.     If Not IsDate(theStartDate) Or Not IsDate(theEndDate) Then '若非法日期，则
   
7.         MyTime = CVErr(xlErrNA) '返回错误值
   
8.         Exit Function
   
9.     End If
   
10.     If theEndDate - theStartDate < 0 Then
    
11.         MyTime = CVErr(xlErrNA) '若发起时间大于结束时间，则返回错误值
    
12.         Exit Function
    
13.     End If
    
14.     theDate1 = DateSerial(Year(theStartDate), Month(theStartDate), Day(theStartDate))
    
15.     theTime1 = theStartDate - theDate1
    
16.     theDate2 = DateSerial(Year(theEndDate), Month(theEndDate), Day(theEndDate))
    
17.     theTime2 = theEndDate - theDate2
    
18.     Select Case theTime1 '确定发起时间（仅指时间部分，不含日期）
    
19.         Case Is < TimeValue("9:00:01") '若发起时间早于上午9点，则确定为9点起始
    
20.             theTime1 = TimeValue("9:00:00")
    
21.         Case Is < TimeValue("12:00:01") '若发起时间早于正午，则theTime1就是发起时间，不用调整
    
22.         Case Is < TimeValue("14:00:01") '若发起时间早于下午14点，则确定为12点起始（此处很重要）
    
23.             theTime1 = TimeValue("12:00:00")
    
24.         Case Is < TimeValue("18:00:01") '若发起时间早于下午18点，则theTime1就是发起时间，不用调整
    
25.         Case Else '否则确定为18:00:00
    
26.             theTime1 = TimeValue("18:00:00")
    
27.     End Select
    
28.     Select Case theTime2 '确定结束时间（仅指时间部分，不含日期）
    
29.         Case Is < TimeValue("9:00:01") '若结束时间早于上午9点，则确定为上午9:00:00
    
30.             theTime2 = TimeValue("9:00:00")
    
31.         Case Is < TimeValue("12:00:01") '若结束时间早于正午，则theTime2就是结束时间，不用调整
    
32.         Case Is < TimeValue("14:00:01") '若结束时间早于下午14点零1分，则确定为中午12点结束（此处很重要）
    
33.             theTime2 = TimeValue("12:00:00") '（承上述）此处故意把结束时间往早前推（不设为14:00:00），
    
34.             '从而与theTime1的12:00:00这一时间点重叠，巧妙地使后续代码的时间相减融为一体，从而不用再专门写代码进行修正
    
35.         Case Is < TimeValue("18:00:01") '若结束时间早于下午18点，则theTime2就是结束时间，不用调整
    
36.         Case Else '否则确定为18:00:00
    
37.             theTime2 = TimeValue("18:00:00")
    
38.     End Select
    
39.     If theDate1 = theDate2 Then '若发起日期与结束日期为同一天，则
    
40.         If MyWorkDay(theDate1) Then '若为工作日，则
    
41.             If theTime1 < TimeValue("12:00:01") Then
    
42.                 If theTime2 < TimeValue("14:00:01") Then
    
43.                     MyTime = theTime2 - theTime1 '两个时间均处于上午，则直接相减
    
44.                 Else
    
45.                     MyTime = TimeValue("12:00:00") - theTime1 '先计算出上午时长
    
46.                     MyTime = MyTime + theTime2 - TimeValue("14:00:00") '再加上下午时长
    
47.                 End If
    
48.             Else '否则，两个时间均处于下午
    
49.                  MyTime = theTime2 - theTime1 '仅下午时长
    
50.             End If
    
51.         End If
    
52.     Else '否则，发起日期与结束日期不为同一天
    
53.         If MyWorkDay(theDate1) Then '先计算发起日的时长
    
54.             If theTime1 < TimeValue("12:00:01") Then
    
55.                 MyTime = TimeValue("12:00:00") - theTime1 '先计算出上午时长
    
56.                 MyTime = MyTime + TimeValue("4:00:00") '再加上下午时长
    
57.             Else '否则，计算发起日的下午时长
    
58.                 MyTime = TimeValue("18:00:00") - theTime1 '仅存在下午时长
    
59.             End If
    
60.         End If
    
61.         If MyWorkDay(theDate2) Then '再计算结束日的时长
    
62.             If theTime2 < TimeValue("12:00:01") Then '计算结束日的上午时长
    
63.                 MyTime = MyTime + theTime2 - TimeValue("9:00:00") '仅存在上午时长
    
64.             Else '否则计算结束日的时长
    
65.                 MyTime = MyTime + TimeValue("3:00:00") + theTime2 - TimeValue("14:00:00") '上午3小时和下午时长
    
66.             End If
    
67.         End If
    
68.     End If
    
69.     For i = theDate1 + 1 To theDate2 - 1 '统计介于发起日和结束日之间的非工作日天数
    
70.         If Not MyWorkDay(CDate(i)) Then
    
71.             n = n + 1
    
72.         End If
    
73.     Next i
    
74.     If theDate2 > theDate1 Then MyTime = MyTime + TimeValue("7:00:00") * (theDate2 - theDate1 - 1 - n) '介于发起日和结束日之间的其他天数必定为7小时
    
75. End Function
    
76. Private Function MyWorkDay(theDate As Date) As Boolean
    
77.     '本函数当前内置数据仅适用于公元2013年
    
78.     Select Case theDate
    
79.         Case #1/1/2013# To #1/3/2013#: Exit Function '元旦
    
80.         Case #2/9/2013# To #2/15/2013#: Exit Function '春节
    
81.         Case #4/4/2013# To #4/6/2013#: Exit Function '清明节
    
82.         Case #4/29/2013# To #5/1/2013#: Exit Function '劳动节
    
83.         Case #6/10/2013# To #6/12/2013#: Exit Function '端午节
    
84.         Case #9/19/2013# To #9/21/2013#: Exit Function '中秋节
    
85.         Case #10/1/2013# To #10/7/2013#: Exit Function '国庆节
    
86.         Case #1/5/2013# To #1/6/2013#, #2/16/2013# To #2/17/2013#, #4/7/2013#, #4/27/2013# To #4/28/2013# _
    
87.         , #6/8/2013# To #6/9/2013#, #9/22/2013#, #9/29/2013#, #10/12/2013#  '调休日
    
88.             MyWorkDay = True: Exit Function
    
89.         Case Else
    
90.             If Weekday(theDate, vbMonday) < 6 Then MyWorkDay = True
    
91.     End Select
    
92. End Function
    

复制代码

王跃峰

yygpdkkk 发表于 2013-4-11 02:09
为此题花了7、8个小时，晕死。
另：代码未经严格测试（短时间内考虑不到那么多细节数据）
代码更新：经再 ...

2013/4/9 7:30        2013/4/16 9:14
计算下来怎么是11:14:00？