Calculate working time between dates in Access (1 Viewer)

I have searched the forum for this answer but no luck.

I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.

I need this time to be calculated in hours.

In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.

At least if i could get the working days i could then convert it into hours.

Please help!

what about public holidays? do you need to count those or exclude them?

Col

your search for "exclude weekends" should have found this, which has a sample in it
link

There are many more examples

Col

hi, i will be looking to exclude public holidays but the most important thing at the moment is that i can exclude non working time - between 3.30pm and 6.30am and weekends.

I must be able to calculate it in hours rather than days though.

Yippee!!

Just thought i would finish off this thread as i finally found some code that works! In case anyone else needs the same kind of help here it is.

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.

At the moment i dont use the holidays part so not sure if that works or not - will update this when i find out.

Syntax error message on query

I am also tryinbg to get a query to give me elapsed time excluding non-working hours and weekends so I tried this module and used a table called 'TblTimes' with a 'requesttime' field and a 'responsetime' field. I changed the appropriate text in the query to

CT Mins (e): workinghours(([tblTimes].[Requesttime],[tblTimes].[responseTime])/60)


however when I tried to run the query I got the error message:

Syntax error (comma) in query expression 'workinghours(((([tblTimes].[Requesttime],[tblTimes].[responseTime])/60)'

How can I get this query to run

Hmm... i'm not exactly sure i'm afraid. I'm good at adapting code but not at fixing it!

The only thing i have noticed is that you seem to have more brackets at the beginning of your code than you have at the end which could cause a problem.

Good luck!

Help! with a date calculation

In my database i have the following

Doc in date 06/05/07, Target days 10, Target Date ????

I have three labels as you can see in the attach photo.

Doc in Date is automatically displays todays date as i put a Date() code
Target Dates is set to default 10
Target Date should automatically display 16/05/2007 after adding Doc in Date + Target Date.


How can this be done? Please help