Calculating Weekdays between two dates

[Marc]

Hi,

I'm trying to calculate the number of weekdays (excluding
Saturday and Sunday) between two dates. I have very little
experience in Visual Basic.
I found some examples online that use a Public Function to
do this.
My problem is I don't know how to connect this to the text
box in my form. I don't understand how to "Call" a
function.
Thanks,

Marc

 

[StCyrM]

Hi Marc

Please find below, a function to calculate workdays, excluding Daturday and
Sunday.

Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

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, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

Hope this is of some help

Best regards

Maurice St-Cyr
Micro Systems COnsultants, Inc

 

[StCyrM]

Sorry Mark

I sent you the function Work_Days but not the way to use it.

In a text box, the Control Source would be as follows:

=Work_Days(Date1, Date2)

Hope this helps

Maurice St-Cyr
Micro Systems Consultants, Inc.

 

[Marc]

Hi Maurice,

Thanks so much - that worked perfectly!

Marc