Calculating the difference between hours

[Sambusa]

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 :confused

 

[Myrna Larson]

If the problem is now that the code runs but the result is wrong, I think this
is the problem:

In your first message on Oct 3, you said "The work hours from 5 AM to 6 PM".
(At least I think it's your first message.)

Based on that statement, I assumed that was ALWAYS true, and wrote the code
with that assumption.

Now you talk about DayStart = 07:30 and DayEnd = 15:30. Are these the correct
times, and are those correct for every case? Or are they completely variable?
Which ever it is, the macro must be modified.

If the workday start and end times are always the same, the constants in the
code must be changed.

If these times are variable, the macro has to be changed to include two more
arguments so you can tell it what those times are.

Please advise.

In the meantime, I'll post code that *requires* that you specify the day start
and day end times.

PS: When writing macros, you can't change the conditions, or assumptions about
conditions (e.g. the times the workday starts and ends), in mid-stream, so to
speak, and expect the code to work.

 

[Myrna Larson]

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