Date Difference in Access VBA

amit_jain76 · January 6th, 2004, 04:26 PM

Hi
I'm trying to calculate workdays(Excluding saturday and sunday) between 2 dates using access VBA but have not been able to do it.Can anyone help me out or point me to the piece of code for doing this.
Thanks
Amit

amit_jain76 · January 13th, 2004, 04:12 PM

i found the solution code for counting workdays between 2 dates:

Dim dif As Integer
Dim i As Integer
Dim dCount As Integer
Dim midTemp

StartDate = "01/05/2004"
enddate = "01/15/2005" '

   dCount = 1
   dif = DateDiff("d", Startdate, enddate, vbMonday, vbFirstJan1)
   For i = 1 To dif
       midTemp = DateAdd("d", i, Startdate)
       If Weekday(midTemp, vbMonday) < 6 Then
            dCount = dCount + 1
        End If
    Next i
    print dCount

SerranoG · January 13th, 2004, 04:43 PM

That code looks interesting but there is a weekday counter option on the DateDiff function. DateDiff("w", Start Date, End Date).

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

amit_jain76 · January 14th, 2004, 11:08 AM

Interestingly DateDiff("w", Start Date, End Date) returns the number of weeks between the two dates as opposed to what the normal behavior would be to count the workdays skipping over the weekends.
-amit

SerranoG · January 14th, 2004, 11:55 AM

Quote:

quote:Originally posted by amit_jain76
Interestingly DateDiff("w", Start Date, End Date) returns the number of weeks between the two dates

Correction: "ww" returns the number of weeks. "w" returns the number of weekdays.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division