I'm using the following code. I'm trying to use this sample to help me calculate hours and minutes. However, some fields maybe blank and it's throwing an error. Can someone help me with this?
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(Nz(dteEnd, 0)) + TimeValue("12:00:00am")
'WorkDayend = DateValue(Nz(dteStart, 0)) + TimeValue("11:59:59pm")
WorkDayStart = DateValue(dteEnd) + TimeValue("12:00:00am")
WorkDayend = DateValue(dteStart) + TimeValue("11:59:59pm")
StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)
'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("[HolidayDate]", "TBL_HOLIDAYS", "[HolidayDate] = #" & 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