Insert the following into a module. I called mine "SLAdatediff".
You need to have a table called "tblHolidays" with a field called
"HolidayDate".
In the query
Expr1: Workingdays2([Startdate],[enddate])
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] = #" & Format(StartDate, "mm-dd-yyyy") & "#"
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
Nigel said:
Yes please.
:
Do you want to exclude weekends and public holidays?
:
Persumably then use the Datediff function?
:
It would be muuuuuch better to have date and time in one field.
I have a form which has a StartDate, StartTime, EndDate and EndTime. I
need
to be able to calculate how long a process takes in hours and minutes, but
it
could start on one day and finish on another. Can it be done with these
fields or would it be better to (if possible) have date and time in one
field?
Thanks in advance
Nigel
