cv****@gmail.com wrote:

Hi all

Im working on productivity calculations (Time calculations) and need

some help in coding.

Database Tool:MS-Access 2003.

The general operator punch-in time is 5:30 AM and the punch-out time is

2:00PM. The Break times are

1) 9:30 AM to 9:45 AM

2) 11:00AM to 11:30 AM

3) 12:30PM to 12:45 PM

Now, suppose an operator starts working on a fresh batch of parts @

7:00AM MONDAY morning and finishes the batch @ 7:00AM TUESDAY Morning,

I need to get answer something like this:

24 hrs(7:00AM Mon - 7:00AM Tue) MINUS 15 Minutes(9:30 AM - 9:45 AM)

MINUS 30 Minutes(11:00AM - 11:30 AM) MINUS 15 Minutes(12:30PM - 12:45

PM) MINUS 15hrs & 30Min (2:00PM Mon till 5:30AM Tue)

That is 7.5 hours.

Im creating a database of operators and 4 different operations and I

need this calculations to be made. The catch is that the operator could

start processing a fresh batch of parts any time of the day and could

finish any given time in a day.

I need to subtract the appropriate break times.

Any help in coding is greatly appreciated.

I would write a public function to count the number of 15 minute breaks

between two general punch-in and punch-outs that include the date and

time. The break from 11:00 AM to 11:30 AM would count as two 15 minute

breaks. Then the number of breaks divided by four should give the

number of hours to subtract for breaks. Try:

Public Function CountBreaks(dtIn As Date, dtOut As Date) As Integer

Dim BreakStart(5) As Date

Dim BreakEnd(5) As Date

Dim dtTest As Date

Dim intI As Integer

Dim NBreaksPerDay As Integer

NBreaksPerDay = 4

CountBreaks = 0

If dtIn >= dtOut Then Exit Function

BreakStart(1) = "9:30 AM"

BreakEnd(1) = "9:45 AM"

BreakStart(2) = "11:00 AM"

BreakEnd(2) = "11:15 AM"

BreakStart(3) = "11:15 AM"

BreakEnd(3) = "11:30 AM"

BreakStart(4) = "12:30 PM"

BreakEnd(4) = "12:45 PM"

'Back up starting time to beginning of 15 minute interval

dtTest = DateAdd("n", -DatePart("n", dtIn) Mod 15, dtIn)

Do While dtTest <= dtOut

For intI = 1 To NBreaksPerDay

If Format(dtTest, "hh:nn") = Format(BreakEnd(intI), "hh:nn") Then

CountBreaks = CountBreaks + 1

End If

Next intI

dtTest = DateAdd("n", 15, dtTest)

Loop

End Function

It would be used in a query something like:

SELECT CountBreaks(PunchIn, PunchOut) / 4 AS BreakTimeHrs,

DateDiff('n', PunchIn, PunchOut) / 60 - [BreakTimeHrs] AS ActualHours

FROM tblTimeTickets;

Perhaps an alternative SQL statement can be constructed that does the

counting instead. I didn't try to get clever by using the date

representation internals in order to provide a shortcut for some of the

calculations. The BreakStart and BreakEnd arrays can also be passed

into the function as arguments. Use the UBound function to get

NBreaksPerDay if you are passing in the arrays. Note: I didn't do much

testing of this function.

Now that we're warmed up, let's apply this to your problem.

For, say, Operator 1, Batch 1 and Operation 1:

PI = 7:00 AM Monday

PO = 2:00 PM Monday

PI = 5:30 AM Tuesday

PO = 7:00 AM Tuesday

So these need to be separate records in tblTimeTickets. Having Access

look for a separate Operation PI to get the PO is a little more

challenging but it's what I'm about to be asked to do.

The first calculation will take one hour away from the seven hours

worked. The second calculation will simply add the 1.5 hours worked

with no BreakTimeHrs subtracted. This suggests something like a query

that uses a SUM of ActualHours with GROUP BY OperatorID, BatchID,

OperationID. There are other potential twists as well that complicate

things when you try to implement this in the real world.

Hope this helps,

James A. Fortune

CD********@FortuneJames.com