[AccessD] MSAccess 2007 - Count days

Hi Kostas

You can copy and paste the code into a new module. Save this and the function can be used anywhere as the built-in functions of Access (like, say, DateDiff).
If this is what is causing you problems, somebody else could perhaps help you with a beginner's guide to VBA (I have none, sorry).

/gustav

>>> kost36 at otenet.gr 16-11-2007 11:23 >>>
Gustav,
could you please show me the way to use that function
I am not really familiar with function's code
thank's a lot
/kostas


----- Original Message ----- 
From: "Gustav Brock" <gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Friday, November 16, 2007 1:17 AM
Subject: Re: [AccessD] MSAccess 2007 - Count days


> Hi Kostas
>
> Here is the function we use:
>
> Public Function ISO_WorkdayDiff( _
>  ByVal datDateFrom As Date, _
>  ByVal datDateTo As Date) _
>  As Long
>
> ' Purpose: Calculate number of working days between dates datDateFrom and 
> datDateTo.
> ' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
> ' Limitation: Does not count for public holidays.
> ' May be freely used and distributed.
> ' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
> ' 2000-10-03. Constants added.
> '             Option for 5 or 6 working days per week added.
>
>  Const cbytWorkdaysOfWeek  As Byte = 5
>
>  Dim bytSunday             As Byte
>  Dim intWeekdayDateFrom    As Integer
>  Dim intWeekdayDateTo      As Integer
>  Dim lngDays               As Long
>  Dim datDateTemp           As Date
>
>  ' Reverse dates if these have been input reversed.
>  If datDateFrom > datDateTo Then
>    datDateTemp = datDateFrom
>    datDateFrom = datDateTo
>    datDateTo = datDateTemp
>  End If
>
>  ' Find ISO weekday for Sunday.
>  bytSunday = WeekDay(vbSunday, vbMonday)
>
>  ' Find weekdays for the dates.
>  intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)
>  intWeekdayDateTo = WeekDay(datDateTo, vbMonday)
>
>  ' Compensate weekdays' value for non-working days (weekends).
>  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = 
> bytSunday)
>  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
>
>  ' Calculate number of working days between the two weekdays, ignoring 
> number of weeks.
>  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * 
> (intWeekdayDateTo < intWeekdayDateFrom))
>  ' Add number of working days between the weeks of the two dates.
>  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, 
> datDateTo, vbMonday, vbFirstFourDays))
>
>  ISO_WorkdayDiff = lngDays
>
> End Function
>
> To exclude holidays, look up and count the holidays from a table of 
> holidays not falling in weekends; substract this number from the count of 
> working days.
>
> /gustav
>
>>>> kost36 at otenet.gr 15-11-07 20:49 >>>
> Hi all,
>
> Does anybody know how it could be possible to count
> the work days between two days excluding weekends and local holidays
> To my coutry there are several dates as holidays and some of them
> are moving year by year
>
> many thank's to all
>
> /kostas