You need to look for a VBA function that is already set up for you without you having to create it from scratch.
Such as this - format is the same as Now():
Code:
Public Function CalcHours(Started, Ended) As Double
Dim StartTime As Date, EndTime As Date, wrkDate As Date, wrkHours As Double
If IsDate(Started) And IsDate(Ended) And Ended > Started Then
Else
Exit Function
End If
If CDate(Format(Started, "mm/dd/yyyy")) = CDate(Format(Ended, "mm/dd/yyyy")) Then
CalcHours = DateDiff("h", Started, Ended)
Else
StartTime = CDate(Format(Started, "mm/dd/yyyy") & " 7:30 am")
EndTime = CDate(Format(Ended, "mm/dd/yyyy") & " 3:30 pm")
wrkDate = CDate(Format(Started, "mm/dd/yyyy")) + 1
wrkHours = DateDiff("n", Started, CDate(Format(Started, "mm/dd/yyyy") & " " & Format(EndTime, "hh:mm ampm")))
Do Until wrkDate >= CDate(Format(Ended, "mm/dd/yyyy"))
If DatePart("w", wrkDate) = 1 Or DatePart("w", wrkDate) = 7 Then
Else
wrkHours = wrkHours + 480
End If
wrkDate = wrkDate + 1
Loop
wrkHours = wrkHours + DateDiff("n", CDate(Format(Ended, "mm/dd/yyyy") & " " & Format(StartTime, "hh:mm ampm")), Ended)
wrkHours = wrkHours / 60
CalcHours = wrkHours
End If
End Function
To utilize this function in a query, add a field such as this:
NbrHrs: CalcHours([starttime],[endtime])
To utilize it in VBA or on a form:
NumHrs = CalcHours(Started, Ended)
If you want to add holidays to the mix, there will have to be a table which contains all the holiday dates for your company.