Calculating difference between two date values

[Bren]

I'm trying to create a module that will calculate the difference between to
date values that will exclude weekends and holidays. I can seem to get the
string to work that's suggested in Microsoft help. Anyone care to share the
module if you done this before? Thanks.

 

[vbasean]

I'm trying to create a module that will calculate the difference between to
date values that will exclude weekends and holidays. I can seem to get the
string to work that's suggested in Microsoft help.  Anyone care to sharethe
module if you done this before? Thanks.

I created a short piece of code to subtract or add to a date and

IF it lands on a sunday add one
IF it lands on a saturday subtract one

so

Public Function ReturnDateNonWeekend(YourDate As Date, AddDays As
Integer) As Date
Dim NewDate As Date
NewDate = DateAdd("d", AddDays, YourDate)
Select Case Weekday(NewDate)
Case vbSunday
NewDate = DateAdd("d", 1, NewDate)
Case vbSaturday
NewDate = DateAdd("d", -1, NewDate)
End Select
ReturnDateNonWeekend = NewDate
End Function

 

[vbasean]

this is neat too:
http://www.ozgrid.com/forum/showthread.php?t=75758

 

[Ken Sheridan]

Bren:

I've seen many solutions to this, but here's one I created some time ago
which caters for different public holidays in different counties using the
following function. In the UK and the Republic of Ireland each constituent
country has its own public holidays as well as the common ones, hence the
need to cater for this. It will also work if you are dealing with just one
country with a single set of public holidays of course; you'd just have one
row in the Countries table and one row per holiday in the PubHols_Country
table:

Public Function WorkDaysDiff(varLastDate As Variant, _
varFirstDate As Variant, _
strCountry As String, _
Optional blnExcludePubHols As Boolean = False)
As Variant

Dim lngDaysDiff As Long, lngWeekendDays As Long
Dim intPubHols As Integer

If IsNull(varLastDate) Or IsNull(varFirstDate) Then
Exit Function
End If

' if first date is Sat or Sun start on following Monday
Select Case WeekDay(varFirstDate, vbMonday)
Case vbSaturday
varFirstDate = varFirstDate + 2
Case vbSunday
varFirstDate = varFirstDate + 1
End Select

' if last date is Sat or Sun finish on following Monday
Select Case WeekDay(varLastDate, vbMonday)
Case vbSaturday
varLastDate = varLastDate + 2
Case vbSunday
varLastDate = varLastDate + 1
End Select

' get total date difference in days
lngDaysDiff = DateDiff("d", varFirstDate, varLastDate)

' get date difference in weeks and multiply by 2
' to get number of weekend days
lngWeekendDays = DateDiff("ww", varFirstDate, varLastDate, vbMonday) * 2

' subtract number of weekend days from total date difference
' to return number of working days
WorkDaysDiff = lngDaysDiff - lngWeekendDays

' exclude public holidays if required
If blnExcludePubHols Then
intPubHols = DCount("*", "qryPubHols", "HolDate Between #" _
& Format(varFirstDate, "mm/dd/yyyy") & "# And #" & _
Format(varLastDate - 1, "mm/dd/yyyy") & "#" & _
" And Country = " & Chr(34) & strCountry & Chr(34))

WorkDaysDiff = WorkDaysDiff - intPubHols
End If

End Function

The DDL for the tables used is as follows:

CREATE TABLE Countries (
CountryID COUNTER,
Country TEXT(50),
CONSTRAINT PrimaryKey PRIMARY KEY (CountryID),
CONSTRAINT Country UNIQUE (Country));

CREATE TABLE PubHols (
HolDateID COUNTER,
HolDate DATETIME,
CONSTRAINT PrimaryKey PRIMARY KEY (HolDateID),
CONSTRAINT HolDate UNIQUE (HolDate));

CREATE TABLE PubHols_Country (
CountryID LONG,
HolDateID LONG,
CONSTRAINT PrimaryKey PRIMARY KEY (CountryID, HolDateID),
CONSTRAINT CountriesPubHols_Country
FOREIGN KEY (CountryID) REFERENCES Countries (CountryID),
CONSTRAINT PubHolsPubHols_Country
FOREIGN KEY (HolDateID) REFERENCES PubHols (HolDateID));

The qryPubHols query used by the function is as follows:

SELECT Countries.Country, PubHols.HolDate
FROM PubHols INNER JOIN (Countries
INNER JOIN PubHols_Country
ON Countries.CountryID = PubHols_Country.CountryID)
ON PubHols.HolDateID = PubHols_Country.HolDateID;

The function is called, passing the start date, end date, country name and,
optionally, True to take account of public holidays. If the optional
argument is omitted then public holidays are not taken account of in
returning the difference in days, only weekends.

If you have any difficulty implementing this I can send you a file which
wraps it all up and includes a form for testing it. Just mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

Ken Sheridan
Stafford, England

 

[Bren]

Ken,
Thanks, for the code. However I got a Compile Error: Syntax Error when I ran
Debug.