Calculate working hours

[Mark256]

Hi

I have created an Access database and I am trying to find out how to calculate the number of hours between two date/time fields, excluding weekends/holidays and non-busness hours during the week.

The closest I have come is this code I copied from another forum. The code itself appears to be sound, but when I run the workinghours calculation in my query I get this error message:

Syntax error (comma) in query expression 'workinghours(([tbl Main Table].[issue alloc date and time],[tbl Main Table].[Time emailed admin])/60)'

Can anyone assist in making this query run?

Regards
Mark

CODE:

Public Function NetWorkhours(dteStart As Date, dteEnd As Date) As Single

Dim intGrossDays As Integer
Dim intGrossHours As Single
Dim dteCurrDate As Date


Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayhours As Single
Dim EndDayhours As Single

NetWorkhours = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day

WorkDayStart = DateValue(dteEnd) + TimeValue("06:30am")
WorkDayend = DateValue(dteStart) + TimeValue("03:30pm")
StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours

If StartDayhours < 0 Then
StartDayhours = 0
End If
If EndDayhours > 8 Then
EndDayhours = 8
End If

'Calculate total hours and days between start and end times

intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossHours = DateDiff("n", (dteStart), (dteEnd))

'count number of weekend days and holidays (from a table called "Holidays" that lists them)
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
nonWorkDays = nonWorkDays + 1
End If
End If
Next i
'Calculate number of work hours

Select Case intGrossDays
Case 0
'start and end time on same day
NetWorkhours = intGrossHours
Case 1
'start and end time on consecutive days
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours
Case Is > 1
'start and end time on non consecutive days
NetWorkhours = NetWorkhours - (nonWorkDays * 1)
NetWorkhours = (intGrossDays - 1 - nonWorkDays) * 8
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours

End Select

You will need to save this as a module called wrkinghrs in your database and change the Workdaystart and Workdayend to your own working hours. When you then go to do a calculation in a query enter it as follows:

CT Mins (e): workinghours(([tbl Main Table].[issue alloc date and time],[tbl Main Table].[Time emailed admin])/60)

This will give you the number of working hours between the two dates.

[June7]

The function is declared as NetWorkhours but the expression is calling a function called workinghours.

Also, the /60 is mispositioned. Try:

NetWorkhours([issue alloc date and time], [Time emailed admin]) / 60

Can you provide link to the code source?

[Mark256]

Thanks June7. It works (I think - the result is in decimal format) but at least there was no error. I'll need to check the result by using a formula I have in excel.

However when I run the query I get a EnterParameterValue window for WorkDays. What value should I be putting in this window?

Here is the link to where I saw the code:

http://www.access-programmers.co.uk/...d.php?t=116721

[June7]

Somewhere in the query is a reference to WorkDays. Access can't find anything by that name so it triggers an input popup. As far as I can tell this has nothing to do with the function.