Calculate Working Days Between Two Dates

G

Greg

Hi Guys,

I'm a novice at this and my problem is that, well, it doesn't work for me!
:) I mean i'm not getting any result in my Days field, not even an error.

I would like to calculate how many working days are between two dates and
also exclude holidays.

Below is the following code pasted into a module called mdlWorkingDays2.
Code obtained from http://www.mvps.org/access/datetime/date0006.htm

I have a form called Leave with the following Fields

LeaveID (primarykey, autonumber)
StaffID (combobox)
StartDate
EndDate
Days

I have a table called tblHolidays with fields

HolidayDate (primarykey)
Holiday



Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function
 
G

Greg

Doug,

Sorry, i've posted the same question twice in this area, i've responded via
the other post "Calculate Working Days Between Dates". Your previous post
was helpful as it prompted me to take a second look at the control source in
Days and i found the problem.

No need reply to this post, however i have a new problem which i have
outlined in my other reply to your post.

Greg.



Douglas J Steele said:
What is the ControlSource for your Days field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Greg said:
Hi Guys,

I'm a novice at this and my problem is that, well, it doesn't work for me!
:) I mean i'm not getting any result in my Days field, not even an error.

I would like to calculate how many working days are between two dates and
also exclude holidays.

Below is the following code pasted into a module called mdlWorkingDays2.
Code obtained from http://www.mvps.org/access/datetime/date0006.htm

I have a form called Leave with the following Fields

LeaveID (primarykey, autonumber)
StaffID (combobox)
StartDate
EndDate
Days

I have a table called tblHolidays with fields

HolidayDate (primarykey)
Holiday



Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top