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,

In your code, change

     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),
use the DateAdd function.  (With the DateAdd function, you SUBTRACT by
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  
 
 [ 3 post ] 

 Relevant Pages 

1. error in calculating elapsed time

2. Need Help Calculating Elapsed Time

3. Calculate elapsed time (in seconds) excluding weekends

4. How to calculate the difference between two dates (time elapsed between two program starts)

5. VB6: Calculating and "Printing" Elapsed Time

6. Calculating elapsed time...

7. Calculating elapsed time

8. Calculating Elapsed Time

9. How do I calculate elapsed time?

10. using stopeatch form functions to time an event procedure and update elapsed time control

11. Elapsed Time between two dates and times

12. Elapsed working time vs. Actual Time

 

 
Powered by phpBB® Forum Software