|
楼主 |
发表于 2019-6-29 01:03
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
【原创】模仿NETWORKDAYS.INTL函数的增强简约版
本帖最后由 YZC51 于 2019-7-5 18:36 编辑
NETWORKDAYS.INTL函数的增强版
Function NETWORKDAYS2INTL(start_date, end_date, Optional weekend = 1, Optional holidays = 0, Optional BreakOff = 0) '2个日期之间工作日数-节假日+调休日
'NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays],[BreakOff])
'start_date 参数 开始日期
'end_date 参数 结束日期
'weekend 参数 每周工作日数
'holidays 参数 法定假日单元格区域
'BreakOff 参数 假日调休单元格区域
'周末数 周末日
'0 整周都是工作日
'1 或省略 星期六、星期日
'2 星期日、星期一
'3 星期一、星期二
'4 星期二、星期三
'5 星期三、星期四
'6 星期四、星期五
'7 星期五、星期六
'11 仅星期日
'12 仅星期一
'13 仅星期二
'14 仅星期三
'15 仅星期四
'16 仅星期五
'17 仅星期六
On Error Resume Next
If weekend = 0 Then NETWORKDAYS2INTL = end_date - start_date: Exit Function
Z = weekend Mod 10: x = 0
If Z > 7 Then NETWORKDAYS2INTL = "#N/A": Exit Function
If end_date > start_date Then yzc = 1 Else yzc = -1 '***
w = Z: w2 = (Z + 5 Mod 7) + 1: x = 0
If Len(weekend) = 1 Then
If end_date = start_date And (Weekday(start_date, 1) <> w And Weekday(start_date, 1) <> w2) Then x = 0: GoTo bb '***2
For i = start_date + 1 To end_date Step yzc
If Weekday(i, 1) <> w And Weekday(i, 1) <> w2 Then x = x + yzc
Next
Else
For i = start_date To end_date Step yzc
If Weekday(i, 2) <> w Then x = x + yzc
Next
End If
If BreakOff.Count = 0 Then GoTo aa
For i = 1 To BreakOff.Count
If BreakOff(i, 1) >= start_date And BreakOff(i, 1) <= end_date Then x = x + yzc
Next
aa:
If holidays.Count = 0 Then NETWORKDAYS2INTL = x: Exit Function
For i = 1 To holidays.Count
If holidays(i, 1) > start_date And holidays(i, 1) <= end_date Then
If Len(weekend) = 1 Then
If Weekday(holidays(i, 1), 1) <> w Then x = x - yzc
If Weekday(holidays(i, 1), 1) = w2 Then x = x + yzc Else
If Weekday(holidays(i, 1), 1) <> w Then x = x - yzc
End If
End If
Next
bb:
NETWORKDAYS2INTL = x
End Function
|
评分
-
3
查看全部评分
-
|