time sums

  • Thread starter Thread starter microsoft
  • Start date Start date
M

microsoft

im trying to set a format of a text box so i can count the number of hours
an employee works i can get it to calculate the differance between 2 times
but if the total goes above 24 hour i get an #error.

can access count time in excess of 24 hours?


thanks in advance for your help


john
 
Yes, but if you're trying to use a "short time" or other format, you'll get
an error if the time that you want to display is more than 24 hours (after
all, there are only twenty-four hours in a day).

Please provide more details about your setup.
 
ive got 6 fields shiftID date, start time, finish time, dlyhrs and tothrs

all of wich are controls on a form (dlthrs and tothrs are unbound) dlyhrs
calculates the diffrence between 2 times and tothrs is supposed to calculate
total hours worked over a week in the format hh:mm (if its possible)

thanks again
john
 
The "trick" to displaying hours and minutes that go beyond "23:59" is to
calculate the time differential in minutes, and then use the minutes to
calculate the full hours and the residual minutes, and then display them
using the Format command in the "hh:nn" format.

Assuming that a field named STime is the start time and ETime is the end
time, use this as the control source to display the time differential in
your desired format:

=Format(DateDiff("n", [STime], [ETime]) \ 60, "0:") & Format(DateDiff("n",
[STime], [ETime]) Mod 60, "00")

To get a weekly total, you'll need to sum the individual time differences
for each day, and then use an expression similar to this for the control
source of a textbox in the form's footer (assuming you have a continuous
forms view):

=Format(Sum(DateDiff("n", [STime], [ETime])) \ 60, "0:") &
Format(Sum(DateDiff("n", [STime], [ETime])) Mod 60, "00")
 
Back
Top