S
Steve
I am trying to calculate the number of days, excluding weekends, between two
dates. I am using the "workingdays" VBA code. I have pasted the code in a
module, naming it "mdlWorkingdays". The code is listed below along with a
sample of the data.
"mdlWorkingdays"
Option Compare Database
'*********** Code Start **************
Public Function Workingdays(Start_Date As Date, End_Date As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays
Dim intCount As Integer
Start_Date = Start_Date + 1
'If you want to count the day of Start_Date as the 1st day
'Comment out the line above
intCount = 0
Do While Start_Date <= End_Date
'Make the above < and not <= to not count the End_Date
Select Case Weekday(Start_Date)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
Start_Date = Start_Date + 1
Loop
Workingdays = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
Sample Data:
Start_Date End_Date
2/14/2006 2/20/2006
2/14/2006 2/21/2006
2/15/2006 2/15/2006
2/10/2006 2/10/2006
I have been trying to use the function in a query. Each time, I get the
following error: "Ambiguous name. in query
'Workingdays([Start_Date],[End_Date])'.
Any help in solving this problem would be greatly appreciated.
dates. I am using the "workingdays" VBA code. I have pasted the code in a
module, naming it "mdlWorkingdays". The code is listed below along with a
sample of the data.
"mdlWorkingdays"
Option Compare Database
'*********** Code Start **************
Public Function Workingdays(Start_Date As Date, End_Date As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays
Dim intCount As Integer
Start_Date = Start_Date + 1
'If you want to count the day of Start_Date as the 1st day
'Comment out the line above
intCount = 0
Do While Start_Date <= End_Date
'Make the above < and not <= to not count the End_Date
Select Case Weekday(Start_Date)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
Start_Date = Start_Date + 1
Loop
Workingdays = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
Sample Data:
Start_Date End_Date
2/14/2006 2/20/2006
2/14/2006 2/21/2006
2/15/2006 2/15/2006
2/10/2006 2/10/2006
I have been trying to use the function in a query. Each time, I get the
following error: "Ambiguous name. in query
'Workingdays([Start_Date],[End_Date])'.
Any help in solving this problem would be greatly appreciated.