Here's a VBA function that returns the number of hours worked as an Excel
time. It assumes that Thursday and Friday are the weekend days. You would
write the worksheet function as
=HoursWorked(A1,B1,Holidays)
where the starting time is in A1, the return time in B1, and Holidays is a
reference to a list of holidays. Format this cell as [h]:mm if you want to see
1:30. If you want to see 1.5, multiply the result * 24, i.e.
=HoursWorked(A1,B1,Holidays)*24
RE your holiday list, you also need to add 2 to the actual calendar date (so
Excel won't subtract for holidays that occur on Thu or Fri), so list the
holidays in one column, in the column to the right use the formula =A1+2, and
specify the second column as the holiday list, not the first.
Option Explicit
Function HoursWorked(StartTime As Date, EndTime As Date, _
Optional Holidays As Range = Nothing) As Double
Dim D1 As Long
Dim D2 As Long
Dim H As Double
Dim N As Long
Dim T As Double
Const WorkdayStart As Double = 5 / 24
Const WorkdayEnd As Double = 18 / 24
Const WorkdayLen As Double = WorkdayEnd - WorkdayStart
D1 = CLng(Int(StartTime))
D2 = CLng(Int(EndTime))
'hours on 1st day
N = GetWorkdays(D1, D1, Holidays)
If N >= 0 Then
T = StartTime - D1
If T < WorkdayStart Then T = WorkdayStart
If T > WorkdayEnd Then T = WorkdayEnd
H = WorkdayEnd - T
End If
'add hours on intervening days
N = GetWorkdays(D1 + 1, D2 - 1, Holidays)
If N > 0 Then H = H + WorkdayLen * N
'add hours on final date
N = GetWorkdays(D2, D2, Holidays)
If N > 0 Then
T = EndTime - D2
If T < WorkdayStart Then T = WorkdayStart
If T > WorkdayEnd Then T = WorkdayEnd
H = H + T - 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 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
Pls u mean i have to write like this
=IF(AND(INT(C2+2)=INT(L2+2),NOT(ISNA(MATC2+2H(INT(C2+2),C9:C10,0))))
,0,ABS(IF(INT(C2+2)=INT(L2+2),ROUND(24*(L2+2-C2+2),2),.....
WAITING !!!
SAMI