calculating time fields

[donnan33]

I have to have a timesheet in my database. I have start time, first time out, first time in, second time out, second time in, finish time. now it is in excel. I have not used the time field before in access just the date fields. i need to have a field to calculate total hours on a daily basis. how can i get it to automatically calculate total hours worked from the field above? please help thanks in advance.

[RayMilhon]

Use the DataDiff Function. You would have to accumulate each period something like
dim hoursworked as double
hoursworked = (DateDiff("m",starttime,firsttimeout) + DateDiff("m",firsttimein,secondtimeout) + DateDiff("m",secondtimein,endtime))/60

I do have one caveat though from your statement your calculating just the time. I am assuming the date is part of the field as well. If not, your calculation will be wrong if any of those time periods cross midnight.

[donnan33]

yes there is a date in a separate field though. there shouldn't be any time after midnight. I will try this out...thanks for your help.

[donnan33]

sent previous to wrong thread. still having trouble. i need a stand alone field for total hours. if i am doing code on what to i set it too?

[donnan33]

still need help!

[Othy]

I have a similar time tracking sheet and while I don't use as many fields as you; I think you can adapt what I'm using to what you need

First, in a query that has your time fields you'll need to create put the following in a new field

Minutes: DateDiff("n",[Time In],[Time Out])

I have mine setup to where it logs the time every time someone opens the DB and every time someone clicks their mouse in the the Time Out Field. This will calculate your time in minutes.

In your report to show total time worked; you'll need something like this

=[Minutes]\60 & Format([Minutes] Mod 60,"\:00")
(minutes is what the previous formula names the new field, you can put a caption on it to change what is displayed)

That will get you the total time worked for that day in an hour format.

To sum it for the week; you can create a text box in the report with the following information:

=Sum([Minutes])/60

That will get you started. I'm still struggling with figuring out how to do a period sum; but hopefully that will get you headed the right direction!