Here's new code. The formula to use is

=HoursWorked(A1,A2,TIME(7,30,0),TIME(15,30,0),Holi days)

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


On Mon, 11 Oct 2004 07:47:42 -0500, Sambusa
wrote:


Thanks for ur prompt respons,

The startDate: 10/6/2004 12:53:26 PM
the EndDate : 10/6/2004 1:45:46 PM

Daystart : 7:30
DayEnd: 15:30


Pls if u can't check the attachment do one example for me pls



Thanks
SAMI