SteveClarkson
Registered User.
- Local time
- Today, 02:00
- Joined
- Feb 1, 2003
- Messages
- 439
Hello all - long time no see! (lucky for all of you!)
I have a working db system that I use a call logging system for an IT helpdesk, which shows the average call resolution time (management just LOVE statistics!).
Anyway - at the moment, I am just using datediff functions, which doesn't make us look very good, if we pick up a call at 4:55pm, and fix it (effectively 10 minutes later) at 9:05am the next morning. The datediff function is picking up all the hours in between, which isn't super.
Anyway - I decided to get this sorted, by knicking other peoples code ;-)
I have the code below, pilfered from this website, but can't make it work in cases where the date changes.
For example, a call logged at 10am, and closed at 11am works fine, it shows 60 minutes.
However, if those same hours are used, but on different days, I get very odd results.
Can anyone spot anything obvious. If it would help, I can post an example of all the code working (or not, as the case may be).
Thanks!
The function I have is:
I have a working db system that I use a call logging system for an IT helpdesk, which shows the average call resolution time (management just LOVE statistics!).
Anyway - at the moment, I am just using datediff functions, which doesn't make us look very good, if we pick up a call at 4:55pm, and fix it (effectively 10 minutes later) at 9:05am the next morning. The datediff function is picking up all the hours in between, which isn't super.
Anyway - I decided to get this sorted, by knicking other peoples code ;-)
I have the code below, pilfered from this website, but can't make it work in cases where the date changes.
For example, a call logged at 10am, and closed at 11am works fine, it shows 60 minutes.
However, if those same hours are used, but on different days, I get very odd results.
Can anyone spot anything obvious. If it would help, I can post an example of all the code working (or not, as the case may be).
Thanks!
The function I have is:
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("09:00am")
WorkDayend = DateValue(dteStart) + TimeValue("05:00pm")
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
End Function