[AccessD] Calculating days

Hi Kostas

> Does anybody keep an example of how to calculate the working days
> inclouding the possible holidays between?

If you dig the archives, functions for calculation working days have
been posted several times. See an example below.

Once calculated these, somehow lookup holidays within the start and
end date and deduct those falling on workdays. This can be as simple
as a table you maintain or - if speed is needed - a function which
calculates the movable (Christian) holidays following moon phases or
other rules. Look up the archives for "Easter".
Fixed holidays like New Year can be hardcoded.

Another method is to create a table of every possible dates for your
system, then mark these as needed. This can seem overwhelming but
think about it: only 10000 records are needed for 30 years.

/gustav

<code>

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

</code>

Beware of line breaks.