cALCULATE HOURS WORKED

[Guest]

How can I calculate time Started and Time Ended times Rate of pay.
I would like to create a time sheet that includes the time a person punch in
for work and the time the punched out..then get the total number hours worked
to the nearest quarter minuit. The calculate that result by the rate of pay.

Hope some can help

Sandrao

 

[Marshall Barton]

How can I calculate time Started and Time Ended times Rate of pay.
I would like to create a time sheet that includes the time a person punch in
for work and the time the punched out..then get the total number hours worked
to the nearest quarter minuit. The calculate that result by the rate of pay.

The number of seconds worked is just:
seconds = DateDiff("s", starttime, endtime)
To round that to the nearest 15 secondsL
seconds4 = Int(seconds + 7.5) / 15) * 15

Then to convert that to hours:
hours = seconds4 / 3600

Then just muliply that by by the pay rate.

 

[Guest]

Sorry, I am still a little confused. Where do I place the code e.g.
=DateDiff("s", starttime,endtime) I place it in a unbound text box and got at
value of
3377232032.00. the starttime was 9:00 endtime was 17:00 which would equal
8:00 or 8 Hours work

What am I doing wrong

 

[John Vinson]

Sorry, I am still a little confused. Where do I place the code e.g.
=DateDiff("s", starttime,endtime) I place it in a unbound text box and got at
value of
3377232032.00. the starttime was 9:00 endtime was 17:00 which would equal
8:00 or 8 Hours work

What am I doing wrong

What are the values of starttime and endtime? It sounds like you've
got a 0 (which is midnight, December 30, 1899) in Starttime:

?dateadd("h",3377232032/3600, #12/30/1899#)
1/6/2007 8:00:00 AM

I had to convert the huge seconds number to hours to avoid overflow
errors!


John W. Vinson[MVP]

 

[Marshall Barton]

Sorry, I am still a little confused. Where do I place the code e.g.
=DateDiff("s", starttime,endtime) I place it in a unbound text box and got at
value of
3377232032.00. the starttime was 9:00 endtime was 17:00 which would equal
8:00 or 8 Hours work

What am I doing wrong

Where you put it and how you calculate it depends on what
you want to do with it.

I suggest that you create a function in a standard module to
calculate the hours value. Then you can use the function
just ablut anywhere in your application.

The code would be pretty much what I posted earlier, but
based on the ridiculous number of seconds above, the
Function should also check for invalid/nonsensical
arguments.

 

[Guest]

The time is =Now() in the starttime
The Now() would equal the time of the morning woker started and the endtime
would be Now() time when worker stopped. My problem is converting the 8hours
worked to a fixed number that can be used to calculate the amount of money
paid for the 8 hours. (times a fixed $ Rate)
My calculations does the math but give it gives me a hour figure "8:00" not
a simple fixed number "8".

Sandrao
So in the

 

[John Vinson]

The time is =Now() in the starttime

That will be today's date plus the time.

The Now() would equal the time of the morning woker started and the endtime
would be Now() time when worker stopped.

Ummmm... No. It wouldn't. Now() gets the current date and time from
the system clock; it has nothing whatsoever to do with when a worker
started or stopped work, or with any table field.

My problem is converting the 8hours
worked to a fixed number that can be used to calculate the amount of money
paid for the 8 hours. (times a fixed $ Rate)
My calculations does the math but give it gives me a hour figure "8:00" not
a simple fixed number "8".

Please post the query in which you're doing the calculation; indicate
what table fields you have and their contents.

If you have a table field named StartTime, containing a date/time
value for when the worker started; another table field named EndTime
containing the date and time that they ended; and you want the hours
worked accurate to the second to be multiplied by a rate,

[Rate] * DateDiff("s", [StartTime], [EndTime]) / 3600.

will get the value. If StartTime or EndTime don't contain that
information, you will of course get some other answer, or no answer at
all!

John W. Vinson[MVP]

 

[Guest]

Your soulition "[Rate] * DateDiff("s", [StartTime], [EndTime]) / 3600"
worked fine. I was able to get the information I needed in the form. But
how can I get a total of hours in a report. How do a total the hour in a
time worked field if we have 5 entries of 7:00 hours. How is that totaled so
that I would have 35 hours worked for the 5 days.

Sandrao

The time is =Now() in the starttime

That will be today's date plus the time.

The Now() would equal the time of the morning woker started and the endtime
would be Now() time when worker stopped.

Ummmm... No. It wouldn't. Now() gets the current date and time from
the system clock; it has nothing whatsoever to do with when a worker
started or stopped work, or with any table field.

My problem is converting the 8hours
worked to a fixed number that can be used to calculate the amount of money
paid for the 8 hours. (times a fixed $ Rate)
My calculations does the math but give it gives me a hour figure "8:00" not
a simple fixed number "8".

Please post the query in which you're doing the calculation; indicate
what table fields you have and their contents.

If you have a table field named StartTime, containing a date/time
value for when the worker started; another table field named EndTime
containing the date and time that they ended; and you want the hours
worked accurate to the second to be multiplied by a rate,

[Rate] * DateDiff("s", [StartTime], [EndTime]) / 3600.

will get the value. If StartTime or EndTime don't contain that
information, you will of course get some other answer, or no answer at
all!

John W. Vinson[MVP]

 

[John Vinson]

Your soulition "[Rate] * DateDiff("s", [StartTime], [EndTime]) / 3600"
worked fine. I was able to get the information I needed in the form. But
how can I get a total of hours in a report. How do a total the hour in a
time worked field if we have 5 entries of 7:00 hours. How is that totaled so
that I would have 35 hours worked for the 5 days.

Put the expression in a calculated field in a Query (by typing
TimeWorked: [Rate] * DateDiff("s", [StartTime], [EndTime]) / 3600
in a vacant Field cell), and base your report on this query. You'll
then be able to display the time worked each day, and sum it in a
Report or section Footer.

John W. Vinson[MVP]