If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating Weekdays between two dates
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 |
#2
|
|||
|
|||
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 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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
Hi Maurice,
Thanks so much - that worked perfectly! Marc -----Original Message----- 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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
calculating (correctly) the weeks and days between dates | neon | Worksheet Functions | 4 | June 28th, 2004 03:07 PM |
Calculate elapsed weekdays between two dates | Frank Kabel | Worksheet Functions | 0 | April 8th, 2004 08:48 PM |
Calculating weekdays | Worksheet Functions | 2 | November 14th, 2003 08:36 AM | |
Calculating age from birth dates | John Nall | Worksheet Functions | 2 | September 15th, 2003 04:39 AM |