为什么我的 CalcWorkingDays VBA 函数在同一时期给我两个不同的结果? [英] Why does my CalcWorkingDays VBA Function give me two different results on the same period?

问题描述

首先，我是初学者，还在学习VBA，感谢您的考虑.

First of all, I'm a beginner and still learning VBA, thank you for your consideration.

我有一个 CalcWorkingDays 函数，它计算特定时间段(由查询参数定义的时间段)内的工作日.

I have a CalcWorkingDays function which which calculates working days within a specific period (period defined by a query parameter).

但是当它返回结果时，在某些时期它是完全正确的，而在其他一些时期它是不正确的(见最后的例子)

But when it returns results, for some periods it is completely correct, and for some others it's incorrect (See example at the end)

我想问题出在这些行中:

I guess the problem is in these lines :

If Format(DateCnt, "w") <> "7" And _
    Format(DateCnt, "w") <> "6" Then

谢谢！

Public Function CalcWorkingDays(BegDate As Variant, EndDate As Variant) As Integer

 Dim WholeWeeks As Variant
 Dim DateCnt As Variant
 Dim EndDays As Integer

 On Error GoTo Err_Work_Days

 BegDate = DateValue(BegDate)
 EndDate = DateValue(EndDate)
 WholeWeeks = DateDiff("w", BegDate, EndDate)
 DateCnt = DateAdd("ww", WholeWeeks, BegDate)
 EndDays = 0

 Do While DateCnt <= EndDate
 If Format(DateCnt, "w") <> "7" And _
 Format(DateCnt, "w") <> "6" Then
 EndDays = EndDays + 1
 End If
 DateCnt = DateAdd("d", 1, DateCnt)
 Loop

 CalcWorkingDays = WholeWeeks * 5 + EndDays

Exit Function

[...]
End Function`

例如，2019 年 3 月.总共有21个工作日.我们有员工 A 和 BA : 他在 01/01/2019 到 31/12/2019 的一个项目中，这个功能给了我 21 个工作日的三月是正确的B : 他从 01/03/2019 到 08/03/2019 被分配到一个项目，它给了我 5，这是不正确的，它应该给我 6(总共 8 天 - 周末 2 天

For example, on march 2019. there is a total of 21 working days. We have both employees A and B A : he's on a project from 01/01/2019 to 31/12/2019, the function gives me 21 working days for march which is correct B : He's been assigned to a project from 01/03/2019 to 08/03/2019, it gives me 5 which is incorrect, it should give me 6 (8 total days days - 2 days for week end

推荐答案

Harassed Dad 是对的——如果你使用 Format(DateCnt, "w")，星期天就会是1"，星期一2"...但是您不应该使用 Format 来获取星期几 - Format 用于将数据格式化为字符串，并且不需要涉及字符串.改用 Weekday 函数.

Harassed Dad is right - if you use Format(DateCnt, "w"), Sunday will be "1", Monday "2"... But you shouldn't use Format to get the day of the week - Format is for formatting data into strings, and there is no need to involve strings. Use the Weekday-function instead.

Weekday 的默认行为是星期日为 1(作为数字，而不是字符串)，但您可以使用第二个参数 (FirstDayOfWeek) 更改它.这定义了您希望将哪一天作为一周的第一天.

The default behavior for Weekday is that Sunday will be 1 (as a number, not a string), but you can change that with the 2nd parameter (FirstDayOfWeek). This defines which day you want to have as first day of the week.

所以你可以改变你的逻辑，例如

So you can change your logic for example to

If Weekday(DateCnt, vbMonday) < 6 Then

这篇关于为什么我的 CalcWorkingDays VBA 函数在同一时期给我两个不同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！