obtaining work hours

[middletree]

I have a request from the boss to make a report that will require me to
display how many hours something has been in a particular state. (As in
status). Hard to explain. Anyway, I need to go to the table in the db, find
all rows for a particular trouble ticket, find when they were put into one
of our 7 statuses, when they were put into another status (this info is
already in those rows), then determine how many hours they spent there in
each status. So a ticket report will say that ticket 1000 spent 4 hours in
customer research, 5 hrs in our company coding. 3 in our company testing, 4
in customer testing, etc.

The thing is, It has to assume an 8-5 workday, and leave out any hours which
don't fall in between 8 am to 5 pm, and also rule out any weekends.

I think that there is probably some sample code out there, because somebody
surely has had to do something similar to this before. If anyone knows of
any, please direct me.

 

[dlbjr]

<%
Set HC = New HourCalc
dblHours = HC.GetWorkHours("10/28/03 8:00:00 AM","11/1/03 12:00:00 PM")
Set HC = Nothing
Response.Write dblHours

Class HourCalc
Private mdblTotalHours
Private mintClockStart
Private mintClockEnd
Private mdtmStartDate
Private mdtmEndDate

Private Sub Class_Initialize()
mdblTotalHours = 0
mintClockStart = 8
mintClockEnd = 17
End Sub

Private Sub Class_Terminate()

End Sub

Public Function GetWorkHours(dtmStart,dtmEnd)
If IsDate(dtmStart) And IsDate(dtmEnd) Then
If CDate(dtmStart) > CDate(dtmEnd) Then
dtmTemp = dtmStart
dtmStart = dtmEnd
dtmEnd = dtmTemp
End If
mdtmStartDate = DateSerial(Year(dtmStart),Month(dtmStart),Day(dtmStart))
mdtmEndDate = DateSerial(Year(dtmEnd),Month(dtmEnd),Day(dtmEnd))
If mdtmStartDate = mdtmEndDate Then
If IsWorkDay(mdtmStartDate) Then
mdblTotalHours = mdblTotalHours + DateDiff("n",dtmStart,dtmEnd) / 60
End If
Else
If IsWorkDay(mdtmStartDate) Then
mdblTotalHours = mdblTotalHours + GetHours(dtmStart)
End If
If IsWorkDay(mdtmEndDate) Then
mdblTotalHours = mdblTotalHours + GetHours(dtmEnd)
End If
intDayDiff = DateDiff("d",mdtmStartDate,mdtmEndDate)
If intDayDiff > 1 Then
For i = 1 To (intDayDiff - 1)
If IsWorkDay(DateAdd("d",i,mintClockStart)) Then
mdblTotalHours = mdblTotalHours + (mintClockEnd - mintClockStart)
End If
Next
End If
End If
End If
GetWorkHours = mdblTotalHours
mdblTotalHours = 0
End Function

Private Function GetHours(dtmItem)
intHour = Hour(dtmItem)
If intHour < mintClockStart Then
intHour = mintClockStart
End If
GetHours = mintClockEnd - intHour
If GetHours < 0 Then
GetHours = 0
End IF
End Function

Private Function IsWorkDay(dtmDate)
IsWorkDay = False
Select Case DatePart("w",dtmDate)
Case 2,3,4,5,6
IsWorkDay = True
End Select
End Function
End Class
%>

-dlbjr

Discerning resolutions for the alms

 

[middletree]

WOW! Did you already have this? Or are you a whiz who can do this stuff on
the fly?

Thanks!

 

[dlbjr]

knocked it out watching the practice last night.
Notice you can adjust the clock start and end in the class.

-dlbjr

Discerning resolutions for the alms

 

[dlbjr]

Did it work to your specifications?

-dlbjr

Discerning resolutions for the alms