NetWorkHours modification

[adamquestion]

Hello,

I am trying to format this code so that I can use it for several applications within my company. I am calculating my elapsed time via the code below. What I would like to incorporate is to have lunch and breaks taken out daily if the Start and Stop time were to encompass the lunch and or breaks. Also, to complicate things, it would be great if the start time for example, were 11/15/08 11:00:00 AM and the End time were 11/17/08 3:00:00 PM.. it would have deducted 1 break and 1 lunch on the 15th, 2 breaks and 1 lunch on the 16th, and 2 breaks and 1 lunch on the 17th, resulting in the total net time. Is this possible? If not, is there another method? Thank You for any help or guidance you can provide.



Public Function NetWorkhours(dteStart As Date, dteEnd As Date) As Single

Dim intGrossDays As Integer
Dim intGrossHours As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayhours As Single
Dim EndDayhours As Single

NetWorkhours = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day

WorkDayStart = DateValue(dteEnd) + TimeValue("06:30am")
WorkDayend = DateValue(dteStart) + TimeValue("03:00pm")
StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours

If StartDayhours < 0 Then
StartDayhours = 0
End If
If EndDayhours > 510 Then
EndDayhours = 510
End If

'Calculate total hours and days between start and end times

intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossHours = DateDiff("n", (dteStart), (dteEnd))

'count number of weekend days and holidays (from a table called "Holidays" that lists them)
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If WeekDay(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
nonWorkDays = nonWorkDays + 1
End If
End If
Next i
'Calculate number of work hours

Select Case intGrossDays
Case 0
'start and end time on same day
NetWorkhours = intGrossHours
Case 1
'start and end time on consecutive days
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours
Case Is > 1
'start and end time on non consecutive days
NetWorkhours = NetWorkhours - (nonWorkDays * 1)
NetWorkhours = (intGrossDays - 1 - nonWorkDays) * 510
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours

End Select

End Function

 

[jwhite]

I have a not-completely-debugged routine to subtract lunches and breaks based on an 8-hour work day. I would post it, but not sure it would work with your requirements. Comments/Questions:

1. Employee Time is calculated based on punch-in and punch-out times.
2. That can be based on minutes or a period of minutes in a given day.
3. Either way, each day has to be calculated separately.
4. The calculations are then based on each day the Employee actually worked.
5. How did you come up with the value "510" ? What does it represent?
6. Is this for Employees or something else?

If 1 -4 above don't apply to your requirements, please describe what your requirements are.

 

[DCrake]

This is code that I provided for another member. However if you work on the principle that the total working minutes in any given day is 510. Therefore if you divide the total minutes by 510 this will give you a ballpark days. You then deduct 2 breaks and one lunch for each day.

If you want to be precise you will first have to build in validation at the data capture stage. Whereby the start times cannot be between the nominated break times or lunch periods. So if the normal break is 10am to 10 15am then trap any times entered during this period. Same for Lunch and afternoon breaks.

However, what happens if different people have different break times and different lunch times. Going to be hard. Even more so if you have shift workers.

 

[adamquestion]

The 510 represents the total minutes between 6:30AM and 3:00PM. I am assigning breaks to the entire department so each individual in the department has the same breaks. My requirements are to provide the time it takes to complete a project that is scanned "started" and "stopped" where the time falls on a break. I have a few other ideas for handling this but I am afraid that any project to carries over to another day will be erroneous. Majority of projects are completed the same day.

 

[DCrake]

If all project are completed in one working day then life is much easier.

You need to establish the start and end point on the time line and if the start comes after the first break then reset the first break value to 0. If the end time comes after the last break time then retain the afternoon break minutes

Morning = 10
Lunch = 30
Afternoon = 10

Total = 510

Elapsed = 510 - ((Morning+Lunch+Afternoon) - duration)

 

[adamquestion]

Thanks DCrake. Unfortunetly I will need a little more help putting that into practice. I am an amateur.