为什么我的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和B A:他在一个项目中，从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

推荐答案

受骚扰的父亲是正确的-如果您使用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屋！