Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the difference between hours
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 -- Sambus ----------------------------------------------------------------------- Sambusa's Profile: http://www.excelforum.com/member.php...fo&userid=1496 View this thread: http://www.excelforum.com/showthread.php?threadid=26588 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the difference between hours
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. The startDate: 10/6/2004 12:53:26 PM the EndDate : 10/6/2004 1:45:46 PM Daystart : 7:30 DayEnd: 15:30 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the difference between hours
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating the difference between hours | Excel Programming | |||
Calculating the difference between hours | Excel Programming | |||
Calculating the difference between hours | Excel Programming | |||
Calculating the difference between hours | Excel Programming | |||
Calculating the difference between hours | Excel Programming |