Apache OpenOffice Community Forum

I have been trying to figure this out for hours with no success. I've looked for templates and can't find what I need.
My company has created a timesheet in Excel, but it has no formulas for adding up the times. It's pretty basic, just a simple, clock in, clock out, subtract lunch type of thing. I've got the daily hours to add up, but when I try to get a subtotal, the results are not even close to right. I'm very confused. I don't know what I'm doing wrong.

Could anyone help with this?
Thanks,
Don

I set the format in H13 to [HH]:MM:SS. This allows the hours to go past 24 if necessary. Does that solve your problem? You will run into problems if a job's Quit Time is past midnight. For example, if you start at 9:00 PM and finish at 3:00AM. Is that a possibility?

Thank you! That is very helpful. I'm not sure what those brackets did, but it solved that part.
I filled in the whole sheet with that formatting, with my addition of the "00 Hours 00 Minutes".
Some things still have me stumped, subtracting the lunch period, and how to convert to decimal time, 4:30 = 4.5 hours.
Also, I noticed a problem with either not entering a start time, or entering PM instead of AM.

Thanks very much!

I put formulas in H13 and H14 to calculated the sum of the times in H8:H12. Notice that the lunch is subtracted. The key to getting an answer in hours, e.g. 6.75, is to understand that times are stored in units of days. 6:00AM has an underlying value of 0.25 of a day. To get to hours, I just multiply by 24.

Thanks! that's very helpful.
When filling this out, I noticed how sometimes I might not want the lunch subtracted, because I'm only counting the hours I worked. But, if I'm working at the same task the whole day, it makes sense to subtract the lunch.
Now I just need a better way to keep track of this during the day.

Thanks again.

This is what I would consider a simple time sheet.
Column A has one date per work day. Duplicate entries get a yellow warning color.
The order of days does not matter. I sorted them randomly.
The formula in E calculates the difference end-start minus lunch plus one day if the end time is before the start time.
Lunch times are not calculated but entered as time.
I added a most simple pivot table to add up times per month. Could be done per week, per day for each employee (if you add another column for the employees).
Insert a new row anywhere in columns A:E of the list in order to enter a new day.
After entering the new values, right-click>Refresh refreshes the pivot table.

Thanks, but that's not what I needed. We need to track which jobs we worked on during a single day. I just wanted to add formulas to the existing time sheet.

Within 2 minutes I come up with the same simple time sheet (plus one column for the jobs) calculating hours per day and per job in a modified pivot table.