# Thread: Calculating Total Hours in Access

1. Registered User
Join Date
May 2006
Posts
9

## Unanswered: Calculating Total Hours in Access

I've been working with this dbase for a week and I have been searching and no luck.

We have 2 shifts, 1st shift starts at 7:00 AM to 5:00 PM and 2nd shift 4:00 PM - 2:00 AM. I have a Timesheet Tab and fields are "Time In1", "Time Out1", "Time In2", "Time Out2".

I am able to get the sub total for each date using this formula "=NZ([Time out1]+1-[Time In1])+NZ([Time Out2]+1-[Time In2])" in the form, query and report. But I am unable to get the total hours for sub total.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
have a look at the datediff function if you subtract the time in 1 from timeout1 and the same for time 2 you should get a value in hours
you can then sum on that

3. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
WHERE are you trying to see the result? Query? Report? Form?
WHERE is that formula? Query? Report? Form?

4. Registered User
Join Date
May 2006
Posts
9
Report and possibly query.

5. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
I am able to get the sub total for each date using this formula "=NZ([Time out1]+1-[Time In1])+NZ([Time Out2]+1-[Time In2])" in the form, query and report. But I am unable to get the total hours for sub total.
And this formula gives your query a calculated field called...... something.

In your report, you can use:

=Sum([something])

To give you a total.

BTW, what is the point in using NZ if you don't supply the value to return when null??

6. Registered User
Join Date
May 2006
Posts
9
I have attached a jpeg of what my report [Individual time Sheet Report] is giving me with "=Sum([Sub Total])" function. 19:00 + 9:00 = 4:00?? I don't get it. Sorry, I'm a newbie!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
so insert a control in the report group footer

=sum(mycolumnname)
where mycolumnname is the name of the column containing the total hours, I think it may also work if you use the name of the control containing the total hours per day

if that doens't work do soem calulations yourself usign a bit of vba behind the report

declare a global variable, say totalhours
in the reports group footer set that varaiable to zero on change of employee (or employee and week, whatever)
add the value of the row hours worked in the detail on format event
in the report group footer assign the value of the variable to a control
jobsagoodun

8. Registered User
Join Date
Feb 2004
Posts
214

## 19+9=4

Access does this because you are displaying hours. Once you reached 24 hours it increments for days by 1 and your hours resets to 0 so that is why it shows 4. There are several ways to do this. Maybe this will help.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
it may be sematincs, but Access is doing this becuase its a dat tiem field, had the OP used datesiff he would be working with intger values, not datetime, so simple mathmatics would work

10. L33t Helpa Munky
Join Date
Nov 2007
Location