Calculating Average in Hours

[G]

Hi
I was hoping you all could help me in this sticky situation!! I've
tried searching all the groups but could'nt find something that would
work.
There is this widget factory, with the standard time it takes to make
one widget. I put in the data about how many widgets a worker has made
over his shift - lets say he's made 50 widgets in 8 hours and it takes
8 minutes to make one widget, so the average time he's taken to make
one widget is - (8 * 60) minutes = 480 / 50 = 9.6 mins per widget. If
one widget has a standard time of 8 minutes, he's done +1.6 mins over
the average.
The trouble I'm having is in calculating how many hours has he gone
over or below. Like, (1.6 min * 50 widgets) = 80 mins over or 1:20. I
can't get around converting 80 mins to hours in Access.
I've tried all sorts of stuff like Format( CDate([AvgMin] * [Parts]),
"Short Time"), but all it does is give me 00:00.
Would be grateful if anybody has any suggestions!!
Thanks!!
Jason

 

[JK]

Time in Access calculated on the basis that 1=1day

1.6*50/1440=0.055555 days

Format the field to ShortTime and you will get 1:20
This will work up to a maximum of 23:59, if you need more format to d:hh:nn

Regards/JK

 

[chickenlittle]

Time in Access calculated on the basis that 1=1day

1.6*50/1440=0.055555 days

Format the field to ShortTime and you will get 1:20
This will work up to a maximum of 23:59, if you need more format t
d:hh:nn

Regards/JK

Hi
I was hoping you all could help me in this sticky situation!! I've
tried searching all the groups but could'nt find something tha would
work.
There is this widget factory, with the standard time it takes t make
one widget. I put in the data about how many widgets a worker ha made
over his shift - lets say he's made 50 widgets in 8 hours and i takes
8 minutes to make one widget, so the average time he's taken to make
one widget is - (8 * 60) minutes = 480 / 50 = 9.6 mins per widget If
one widget has a standard time of 8 minutes, he's done +1.6 min over
the average.
The trouble I'm having is in calculating how many hours has he gone
over or below. Like, (1.6 min * 50 widgets) = 80 mins over or 1:20 I
can't get around converting 80 mins to hours in Access.
I've tried all sorts of stuff like Format( CDate([AvgMin] [Parts]),
"Short Time"), but all it does is give me 00:00.
Would be grateful if anybody has any suggestions!!
Thanks!!
Jason

Try this:
Using your widget worker table (assuming this is what you have)

worker hours widgets
Tom 8 50
Dick 8 45
Jane 8 50

develop a query such as

SELECT widgets.worker, widgets.hours, widgets.widgets
([hours]*60)/[widgets] AS ave, ([ave]-8) AS diff, [diff]*50 AS change
HoursandMinutes([change]/60) AS changehours
FROM widgets;

This is the Microsoft function to calculate hours and minutes.


Public Function HoursAndMinutes(interval As Variant) As String

***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string

***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

Hope it helps

 

[G]

Thanks a ton JK & chickenlittle! The code works like a charm, it's
solved a big headache for me

Regards
G