# Thread: Compute Time In Ms Access

1. Registered User
Join Date
May 2004
Posts
1

## Unanswered: Compute Time In Ms Access

I am trying to setup a database that will calculate cycle time. (I.E. 1st shift started on job "xyz" @ 2 pm and ended at 5pm = 3 hours of time spend on the job.) I am new to Access and have my table and form the way I want it. Does anyone have a formula that will work and where do I input this formula. My fields for time are Start time, End time. Any help is much appreciated. Thanks.

2. (Making Your Life Easy)
Join Date
Feb 2004
Location
New Zealand
Posts
1,469
Provided Answers: 10
Originally Posted by bambam
I am trying to setup a database that will calculate cycle time. (I.E. 1st shift started on job "xyz" @ 2 pm and ended at 5pm = 3 hours of time spend on the job.) I am new to Access and have my table and form the way I want it. Does anyone have a formula that will work and where do I input this formula. My fields for time are Start time, End time. Any help is much appreciated. Thanks.
http://www.dbforums.com/t995623.html

it got some time stuff in it

3. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
If you only need to cover a 24 hour period this is a simple way that I use in a telemarketing data base.

Assume your "sign on time" is [Time 1] and "sign off time is [Time 2]

Make a query on your table and create two calculated fields as such

FirstCalc: ([Time 2]-[Time 1])*24

FinalCalc: IIf([FirstCalc]<0,24+[FirstCalc],[FirstCalc])

If you have not made calculate fields all that you do is type the field name in one of the blank columns after you have dragged down your table fields and then add a colon that is a :

In the above example the FirstCalc gives a negative answer if the sign on time is a pm and sign off is an am time

You now base your form on the query you have made and then add the field [FinalCalc] to your form.

If you do not want to make a query for your form's data source then you add three unbound text boxes on your form and for their data you enter the above preceded by = such as

=([Time 2]-[Time 1])*24

assuming the above textbox has been named FirstCalc then the seond textbox has entered for its data source

=IIf([FirstCalc]<0,24+[FirstCalc],[FirstCalc]) That will give the hours for each individual record displayed on your form

If the above textbox has been called FirstCalc then in the third unbound textbox you enter in its data source

=Sum([FinalCalc]) That will give the total number of hours for all of your records displayed in your form

After you have finished then give the first text box, that is the one with
=([Time 2]-[Time 1])*24 as its data source, zero dimensions for top, width, left and height and you will no longer be able to see it.

Mike

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•