[AccessD] difference in days

Hi Pedro

If you browse 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.


>>> pedro at plex.nl 20-10-2006 13:10:14 >>>
Hallo Group,

i used the following sql to calculate the difference between two dates.

SELECT Datum.Rapnaam, DateDiff("d",[DatOnt],[DatScr]) AS verschil
FROM Datum;

but i forgot that i only need the difference in workingdays, without the weekends. 

Is there a way to adjust the sql?

Pedro Janssen