Calculating elapsed time and adding time values
Author Message Calculating elapsed time and adding time values

I'm using Access '97.  My single biggest problem is in handling times
and doing math functions with them.

I want to have users record their TimeIn (e.g., 08:00 AM) and TimeOut
(e.g., 05:00 PM), take the difference, subtract standard times for
Breaks and Lunch, and return a value for the total elapsed time worked.
Then I want to sum the daily times for a weekly and monthly report, find
out if they worked over 40 hours a week, and use these values in a
report.  If I must convert the daily elapsed times to some other Variant
Type in order to sum the values, to what would I convert them and how?

Here is my code.  Any help would be appreciated.  Why can't Microsoft
provide better documentation and explanations for dealing with time and
date calculations???  This is an event procedure in  the After_Update
control of the [Out] field

Private Sub Out_AfterUpdate()

Dim LunchTime As Date
Dim BreakTime As Date
Dim GrossTime As Date

LunchTime = TimeValue("00:30")
BreakTime = TimeValue("00:10")
GrossTime = [Out] - [In]

If GrossTime >= "08:00" Then
[TotalTime] = ((GrossTime) - ((BreakTime * 2) + LunchTime))
ElseIf GrossTime >= "06:00" Then
[TotalTime] = ((GrossTime) - ((BreakTime) + LunchTime))
ElseIf GrossTime > "04:00" Then
[TotalTime] = ((GrossTime) - BreakTime)
ElseIf GrossTime <= "04:00" Then
[TotalTime] = (GrossTime)
End If

End Sub

Mon, 17 Jul 2000 03:00:00 GMT  Calculating elapsed time and adding time values

On Thu, 29 Jan 1998 11:14:44 -0500, John Glasscock

Quote:

>I want to have users record their TimeIn (e.g., 08:00 AM) and TimeOut
>(e.g., 05:00 PM), take the difference, subtract standard times for
>Breaks and Lunch, and return a value for the total elapsed time worked.

>Here is my code.  Any help would be appreciated.  Why can't Microsoft

It's easier than most people suppose: times are only doubles with the
date part in front of the decimal, and the time (as a fraction of a
day) to the right. If you want just the time of day from a date-time,
you can use MyDateVariable - Int(MyDateVariable) to get the fractional
part.

You can add them, average them, take the square root of them to your
heart's content. Remember to use Format(SomeTime, "hh:mm:ss") or
"ShortTime" or something for output.

Try this:

Private Sub Out_AfterUpdate()

Dim GrossTime As Double

Const LunchTime As Double = 1.0 / 24 / 2   ' = half an hour
Const BreakTime As Double = 1.0 / 24 / 6   ' = ten minutes
Const EightHours As Double = 1.0 / 24 * 8
Const SixHours As Double = 1.0 / 24 * 6
Const FourHours As Double = 1.0 / 24 * 4

GrossTime = [Out] - [In]                   ' This still works fine
' And the rest is
If GrossTime >=  EightHours Then           ' self-documenting...
[TotalTime] = GrossTime - BreakTime * 2 - LunchTime

ElseIf GrossTime >= SixHours Then
[TotalTime] = GrossTime - BreakTime - LunchTime

ElseIf GrossTime > FourHours Then
[TotalTime] = GrossTime - BreakTime

ElseIf GrossTime <= FourHours Then
[TotalTime] = GrossTime

End If

End Sub

All the best

Tim Ferguson

Mon, 17 Jul 2000 03:00:00 GMT  Calculating elapsed time and adding time values

Hi John,

Dim LunchTime As Date
Dim BreakTime As Date
Dim GrossTime As Date

to Integer data types, since these times are not really "times", they
are NUMBER of units of time (for example, number of minutes).

Keep all number units the same (for example, all in minutes).

Then instead of (Time In - Time Out) to figure then number of minutes on
the job (which you can then divide by 60 to get the number of hours),
using a negative number).

Hope this helps.  Feel free to contact me e-mail if you need further
info.

Brenda

Quote:
> Private Sub Out_AfterUpdate()

>     Dim LunchTime As Date
>     Dim BreakTime As Date
>     Dim GrossTime As Date

>     LunchTime = TimeValue("00:30")
>     BreakTime = TimeValue("00:10")
>     GrossTime = [Out] - [In]

>     If GrossTime >= "08:00" Then
>         [TotalTime] = ((GrossTime) - ((BreakTime * 2) + LunchTime))
>     ElseIf GrossTime >= "06:00" Then
>          [TotalTime] = ((GrossTime) - ((BreakTime) + LunchTime))
>     ElseIf GrossTime > "04:00" Then
>          [TotalTime] = ((GrossTime) - BreakTime)
>      ElseIf GrossTime <= "04:00" Then
>          [TotalTime] = (GrossTime)
>     End If

> End Sub

Thu, 20 Jul 2000 03:00:00 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages