Calculating Total Time on a Form in HH:MM Format

[tsroque]

I'm working on a Timesheet database where I have a WeekEnding Form with a subform_Time linked to it. This form is then linked to an employee (subform within a subform type set up).

In the subform_Time I have the following fields:
- DateWorked
- TimeIn (format: Med Time HH:MM)
- LunchOut (format: Med Time HH:MM)
- LunchIn (format: Med Time HH:MM)
- TimeOut (format: Med Time HH:MM)
- Total Lunch Hrs in formate mm
- Total Hrs Worked (for the day) displays as hh:mm (Field Name: Worked)

All the above works great but I need a SUM field to add up the total HH:MM for the x-number of dates I enter for that employee for that WeekEnding. So at the bottom, I need the Total Hrs Worked summed up in the format hh:mm so I can then calculate total overtime due in formate hh:mm in another field.

The difference between TimeIn/LunchOut and LunchIn/TimeOut is calculated in a Query to give me the amount in WORKED field. There are never a set number of days in a Weekending record. Some guys may clock in and out twice that day if on call, etc...

When I use the simple SUM function, it gives me an error. Can anyone help me with this? My Access is soooooo rusty!!!

 

[revans]

Sounds like you want our friend DSum. Something like this might work.

DSum([DailyTotal],"MyTimeTable","[TheDate] Between #1/1/2011# AND #1/7/2011#")

You might have issues with telling DSum your [DailyTotal] field as it is a sum field itself. You might have to put the field's control source in there instead. Think of DSum as a query crammed into a function and that might help you figure out what each argument needs.

hth,

Rich

 

[tsroque]

Hi REVANS!

After reading a few articles, I based it on the time difference in my query which is in a number format. My WORKED field in in Med Time format. So here's what I got:

My query calculate the time difference between TimeIn and LunchOut minus LunchIn and TimeOut. This gives me DiffTimeLunch for each occurence or record. So on my form, I needed to see a TotalTime for that WeekEnding record:

TotalTime =Sum([DiffTimeLunch])\60 & Format(Sum([DiffTimeLunch]) Mod 60,"\:00")

Now I need to know how to calculate Overtime IF my DiffTimeLunch is over 2400, and it needs to be in the same format. It just needs to show OT hours. Any clue?

Thank you!!!

 

[tsroque]

I think I got it, but if anyone knows how to combine it to one field, I'd appreciate it...just to have a cleaner form

Anyways, I created an OTCal invisible field that will pull only the overtime minutes out:
OTCal =IIf(Sum([DiffTimeLunch])>2400,Sum([DiffTimeLunch])-2400,Sum([DiffTimeLunch]))

Then using the same format as described above, I display the overtime field as such:
OT =[OTCal]\60 & Format([OTCal] Mod 60,"\:00")

It works, but always looking for a better mousetrap or if anyone can think of any complications I'm overlooking using this format on my form and reports, I'd appreciate it.

 

[revans]

I hate time.

One idea for keeping it all in one field is to use the full date and time. Then you can do a simple DateDiff() to calculate hours. Of course, this can be harder to enter manually if that's what you're doing.

You could also just include the whole formula from your hidden field in your second field. It's harder to read but it's in one formula.

hth,

Rich