date - Why does my CalcWorkingDays VBA Function give me two different results on the same period?

Question

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

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

Thank you !

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`

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

Answer 1

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.

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