DateDiff function not calculating week days correctly.

  • Thread starter Thread starter Lisa-Marie
  • Start date Start date
L

Lisa-Marie

I've been everywhere researching how to calculate total
week days (work days)between the start_date and end_date
using the following: Exp:datediff("w",[start_date],
[end_date]). I used "d" first for days, then realized it
didn't exclude weekends, so I researched some more to find
the above mentioned expression. Well, it didn't work and
according to MS Knowledge Base Article - 207795 I know
why. The above expression calculates the number of weeks,
not week days (like it's suppose to). I need a work
around, can anyone help me. I'm running Access 2000 and I
don't have access to SQL, I need to run it as an
expression. Any suggestions?
 
Here's a "brute-force" function (found in another newsgroup) for doing what
you seek:

Public Function NumberOfWeekDays(datStart As Date, datEnd As Date) As Long
Dim lngNumber As Long, lngTotalDays As Long, lngCount As Long
lngTotalDays = datEnd - datStart
lngNumber = 0
For lngCount = 1 To lngTotalDays
If DatePart("w", datStart + lngCount, vbMonday) < 6 Then lngNumber =
lngNumber + 1
Next lngCount
NumberOfWeekDays = lngNumber
End Function
 
Back
Top