Subtract MULTIPLE time segments from a single time segment to calculate cycle time
cvi...@gmail.com
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.
Mike Preston
I'm beginning to sound like a broken record:
What have you tried to do? Show your work. How is it not working?
mike
Dean
something like
DateDiff("d",[Start Time],[End Time])*24+Hour([End Time]-[Start
Time])+Minute([End Time]-[Start Time])/60
[Start Time] and [End Time] includes a date as well. For example
12/19/2005 07:00:00 AM and 12/20/2005 07:00:00 AM.
Now if you have users "clocking" in and out of Access look at the
timer() function. This calulates the number of seconds after midnight
and you can store that in a table. Real easy to calculate time but
assumes the date is the same so you have to work around that.
Again, I don't have all the bugs worked out, but maybe this will help.
CDMAP...@fortunejames.com
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
CDMAP...@FortuneJames.com
CDMAP...@fortunejames.com
> cvi...@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.
> 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.
Here's a rudimentary and nearly totally untested start at using SQL to
do the same thing:
tblTimeTickets
TTID AutoNumber
PunchIn Date/Time
PunchOut Date/Time
TTID PunchIn PunchOut
1 12/19/05 9:15:00 AM 12/23/05 10:30:00 AM
2 12/22/05 9:30:00 AM 12/23/05 5:30:00 PM
tblBreaks
BID AutoNumber
BreakStart Date/Time formatted h:nn ampm
BreakEnd Date/Time formatted h:nn ampm
BID BreakStart BreakEnd
1 9:30 AM 9:45 AM
2 11:00 AM 11:15 AM
3 11:15 AM 11:30 AM
4 12:30 PM 12:45 PM
qryCountBreaks:
SELECT TTID, BreakStart, BreakEnd, IIf(Abs(Int(PunchIn)+(BreakEnd)
Between PunchIn And
Punchout)=0,0,DateDiff('n',Int(PunchIn)+(BreakEnd),PunchOut)\60\24)+1
AS CountBreaks FROM tblTimeTickets, tblBreaks;
!qryCountBreaks:
TTID BreakStart BreakEnd CountBreaks
1 9:30 AM 9:45 AM 5
2 9:30 AM 9:45 AM 2
1 11:00 AM 11:15 AM 4
2 11:00 AM 11:15 AM 2
1 11:15 AM 11:30 AM 4
2 11:15 AM 11:30 AM 2
1 12:30 PM 12:45 PM 4
2 12:30 PM 12:45 PM 2
Analysis:
The query is not quite correct as it stands. It uses the BreakEnd
value on the day of PunchIn to see if the value lies between the
PunchIn and PunchOut. If it does, that one is counted and one more is
added for each 24 hour period after that BreakEnd value that is
contained between PunchIn and PunchOut. A BreakEnd time slightly after
midnight needs to be considered. The test example using some poor
workaholics avoids some obvious problems conveniently. I also didn't
test to see if 59.75 minutes gets converted to 60 by the DateDiff
function. DateDiff using hours proved unsatisfactory. I probably
should have gone directly to the representation of dates as a Double
value to determine this difference. Post back if you need more hints.
James A. Fortune
CDMAP...@FortuneJames.com
cvi...@gmail.com
This is pure Genius. It works. Let me play around it a little more and
ask for help if I need some thing. Thanks a Million again.