Calculating the difference between hours

[Sambusa]

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 !!!
SAM

 

[Myrna Larson]

You only need to add 2 when you use the dates as arguments in NETWORKDAYS
function. I don't see that function in the part of the formula you show.

 

[Myrna Larson]

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

 

[Myrna Larson]

I realized after posting the code below that I forgot to include the "special
case" where the start time and end time are on the same day. It will not give
correct results for that situation. I won't be able to correct my oversight
for about 48 hours.

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

 

[Myrna Larson]

Here's the "final" code. It requires that you have installed the Analysis Tool
Pack. If you haven't done that, the code won't compile.

To use this, go to the VB Editor (ALT+F11) and select your workbook over in
the upper left hand project pane.

Then go to the Tools menu, select References, and put a check mark in front of
ATPVBAEN.XLA, or whatever it's named in your version (the 'EN' may be
different).

Then insert a module and paste all of the code below (beginning with the line
Option Explicit) into the large code window you see on the right side of the
screen.

Then write a formula like HoursWorked(A1,A2,K1:K10) where the starting date
and time are in A1, the ending date and time are in A2, and K1:K10 contains a
list of legal holidays (with 2 added to each calendar date to handle the fact
that your weekend days are Thu and Fri rather than Sat and Sun). Note that the
holidays must be in a range. I didn't spend time on the extra coding needed to
handle a literal array of holidays.


Option Explicit

Private Const WorkdayStart As Double = 5 / 24
Private Const WorkdayEnd As Double = 18 / 24
Private Const WorkdayLen As Double = WorkdayEnd - WorkdayStart

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 T1 As Double
Dim T2 As Double

D1 = CLng(Int(StartTime))
T1 = ValidTime(StartTime)
D2 = CLng(Int(EndTime))
T2 = ValidTime(EndTime)

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(D As Date) As Double
'given a date and time, isolate the time portion
'and constrain to limits of the work day
Dim tt As Double

tt = D - Int(D)
If tt < WorkdayStart Then tt = WorkdayStart
If tt > WorkdayEnd Then tt = WorkdayEnd
ValidTime = tt
End Function