469,276 Members | 1,774 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,276 developers. It's quick & easy.

obtaining work hours

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.
Jul 19 '05 #1
4 1917
<%
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(dtmS tart))
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
Jul 19 '05 #2
WOW! Did you already have this? Or are you a whiz who can do this stuff on
the fly?

Thanks!
"dlbjr" <do******@do.u> wrote in message
news:89Erb.174$Qy4.13989@typhoon01...
<%
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(dtmS tart)) 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

Jul 19 '05 #3
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
Jul 19 '05 #4
Did it work to your specifications?

-dlbjr

Discerning resolutions for the alms
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by __jakal__ | last post: by
2 posts views Thread by amirmira | last post: by
1 post views Thread by Dave | last post: by
5 posts views Thread by zion_zii | last post: by
11 posts views Thread by seannakasone | last post: by
1 post views Thread by Stephen Poley | last post: by
7 posts views Thread by =?Utf-8?B?Sm9obiBTdGFnZ3M=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.