Calculating the Number of Days between two dates

I need to calculate the number of working days (MondayFriday) between two
dates, for example 10/07/08 - 18/07/08, would be a total of 7 working days.
On Excel this is simply done by using networkdays, but how do i go about
doing this on Access. Im not too fussed about bank holidays, but if there
are any geniuses out there that know how to do this as well that would be a
great help.

This site has a function you can use:
http://www.mvps.org/access/datetime/date0012.htm

Damon

"
wrote in message ...
I need to calculate the number of working days (MondayFriday) between two
dates, for example 10/07/08 - 18/07/08, would be a total of 7 working
days.
On Excel this is simply done by using networkdays, but how do i go about
doing this on Access. Im not too fussed about bank holidays, but if there
are any geniuses out there that know how to do this as well that would be
a
great help.

Here's a bare-bones solution based on a 5-day work week. Holidays are not
considered.

x = #1/1/08#
y = #12/31/08#

? 7 - weekday(x) + 5*(datediff("w", x, y)-1) + weekday(y) - 1

262

HTH - Bob


wrote:
I need to calculate the number of working days (MondayFriday) between two
dates, for example 10/07/08 - 18/07/08, would be a total of 7 working days.
On Excel this is simply done by using networkdays, but how do i go about
doing this on Access. Im not too fussed about bank holidays, but if there
are any geniuses out there that know how to do this as well that would be a
great help.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

The following function does this, and also caters, optionally, for public
holidays. Moreover it handles the different public holidays in the
constituent counties of the UK and of the Republic of Ireland by means of the
Country argument.

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 = """ & strCountry & """")

WorkDaysDiff = WorkDaysDiff - intPubHols
End If

End Function

The qryPubHols query referred to in the function is:

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;

As you see this is based on 3 tables: Countries with columns CountryID and
Country,
PubHols_Country with columns CountryID and HolDateID, and PubHols with
columns HolDateID and HolDate. It was first written many years ago; if I
were doing it now I'd get rid of the HolDateID and CountryID columns and use
Country and HolDate as the keys. The query would not then only need to
include the PubHols_Country table. In fact the table itself could be the
domain argument of the DCount function.

If you are only concerned with one set of UK public holidays you can remove
the Country argument from the function and just have a single-column PubHols
table. On the other hand, while it was originally designed for UK/Republic
of Ireland use, it can be extended internationally simply by inserting more
rows into the three tables with additional countries and their public holiday
dates.

Ken Sheridan
Stafford, England

" wrote:

I need to calculate the number of working days (MondayFriday) between two
dates, for example 10/07/08 - 18/07/08, would be a total of 7 working days.
On Excel this is simply done by using networkdays, but how do i go about
doing this on Access. Im not too fussed about bank holidays, but if there
are any geniuses out there that know how to do this as well that would be a
great help.

Correction: Function should have been:

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, vbSunday)
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, vbSunday)
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 = """ & strCountry & """")

WorkDaysDiff = WorkDaysDiff - intPubHols
End If

End Function

One other thing I should have mentioned. The function works like the built
in DateDiff function (hence its name) and counts exclusive of the last date,
so 10/07/08 - 18/07/08 would be 6 working days, not 7. To make it inclusive
amend it like so:

If IsNull(varLastDate) Or IsNull(varFirstDate) Then
Exit Function
Else
varLastDate = DateAdd("d", 1, varLastDate)
End If

Ken Sheridan
Stafford, England