Here's new code. The formula to use is
=HoursWorked(A1,A2,TIME(7,30,0),TIME(15,30,0),Holidays)
Note that the start and end of the workday are required. You could put the
times in two other cells, say F1 and F2, and refer to those cells in the
formula.
For the example you gave, the result is 52 minutes and 20 seconds.
Option Explicit
Function HoursWorked(StartTime As Date, EndTime As Date, _
WorkdayStart As Date, WorkdayEnd As Date, _
Optional Holidays As Range = Nothing) As Variant
Dim D1 As Long
Dim D2 As Long
Dim H As Double
Dim N As Long
Dim T1 As Double
Dim T2 As Double
Dim WorkdayLen As Double
HoursWorked = CVErr(xlErrValue)
WorkdayLen = WorkdayEnd - WorkdayStart
If WorkdayLen <= 0 Then Exit Function 'times are reversed
D1 = CLng(Int(StartTime))
T1 = ValidTime(StartTime, WorkdayStart, WorkdayEnd)
D2 = CLng(Int(EndTime))
T2 = ValidTime(EndTime, WorkdayStart, WorkdayEnd)
If D2 < D1 Then Exit Function 'dates are reversed
H = 0
If D2 = D1 Then 'start and finish on same day
N = GetWorkdays(D1, D1, Holidays)
If (N > 0) And (T2 > T1) Then H = T2 - T1
ElseIf D1 < D2 Then 'finish on a later day
'hours for first (partial?) day:
'start at T1, end at end of workday
N = GetWorkdays(D1, D1, Holidays)
If N > 0 Then H = WorkdayEnd - T1
'hours for full workdays, D1+1 through D2-1, inclusive
N = GetWorkdays(D1 + 1, D2 - 1, Holidays)
If N > 0 Then H = H + N * WorkdayLen
'hours for final (partial?) day:
'start at beginning of workday, end at T2
N = GetWorkdays(D2, D2, Holidays)
If N > 0 Then H = H + T2 - WorkdayStart
End If
HoursWorked = H
End Function
Private Function GetWorkdays(Date1 As Long, Date2 As Long, _
Optional Holidays As Range = Nothing) As Long
'NB: Thursday and Friday are weekend days, so add 2 to the dates
'when calling NETWORKDAYS so the ATP function will think Thu and Fri
'are Sat and Sun, and thus not working days
If Holidays Is Nothing Then
GetWorkdays = NETWORKDAYS(Date1 + 2, Date2 + 2)
Else
GetWorkdays = NETWORKDAYS(Date1 + 2, Date2 + 2, Holidays)
End If
End Function
Private Function ValidTime(DateAndTime As Date, _
StartTime As Date, EndTime) As Double
'given a date and time, isolate the time portion
'and constrain to limits of the work day
Dim tt As Double
tt = DateAndTime - Int(DateAndTime)
If tt < StartTime Then tt = StartTime
If tt > EndTime Then tt = EndTime
ValidTime = tt
End Function