My application is about booking faults and then closing them when they have been resolved.
I need to calculate how long a fault has been open from the Time that it was assigned to a Team to the current Time or time it was closed.
Here's the tricky part:
1. The calculation should only calculate for the time period 08H00 to 16H00 of the week day, i.e. Monday to Friday. Example if I log a fault on Thursday at 15H30, the duration that it is open for is an hour at 08H00 on Friday morning.
2. The calculation should exclude weekends, i.e. Saturday and Sunday.
3. The calculation should exclude public holidays. (some where I have to define these days in order for my system to pick it up)
Any ideas as to how the problem will be solved?
Thnx
Public Function BookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As Long
' constants that indicate the working day
Const WORKING_DAY_START = "08:00"
Const WORKING_DAY_END = "16:00"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck as recordset
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("m", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
set snpCheck = currentdb().openrecordset(
if snpcheck.recordcount = 0 then
intDays = intDays + 1
end if
snpcheck.close
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays > 2 Then
intHours = intHours + ((intDays - 1) * HOURS_IN_DAY)
intMinutes = intMinutes + ((intDays - 1) * (MINUTES_IN_DAY Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' finally, tidy up the result
If intHours >= HOURS_IN_DAY Then ' add to the days
intDays = intDays + (intHours / HOURS_IN_DAY)
intHours = intHours Mod HOURS_IN_DAY
End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
BookedHours = CStr((intDays*HOURS_IN_DAY
End Function
HTH
Simon