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
[Solved] Simple timesheet
-
- Posts: 4
- Joined: Mon Mar 23, 2020 12:15 am
[Solved] Simple timesheet
- Attachments
-
- TIME SHEET blank.ods
- Timesheet as is now.
- (15.91 KiB) Downloaded 277 times
Last edited by Spaceboy64 on Thu Mar 26, 2020 11:37 pm, edited 1 time in total.
Open Office 3.4.1 on Windows 7
Re: Simple timesheet
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?
- Attachments
-
- TimeSheet_fjcc.ods
- (17.71 KiB) Downloaded 179 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 4
- Joined: Mon Mar 23, 2020 12:15 am
Re: Simple timesheet
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 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!
- Attachments
-
- TimeSheet_fjcc2.ods
- (16.09 KiB) Downloaded 143 times
Open Office 3.4.1 on Windows 7
Re: Simple timesheet
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.
- Attachments
-
- TimeSheet2_fjcc.ods
- (18.05 KiB) Downloaded 157 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 4
- Joined: Mon Mar 23, 2020 12:15 am
Re: Simple timesheet
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.
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.
Open Office 3.4.1 on Windows 7
Re: Simple timesheet
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.
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.
- Attachments
-
- simple_timesheet.ods
- (33.1 KiB) Downloaded 220 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 4
- Joined: Mon Mar 23, 2020 12:15 am
Re: Simple timesheet
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.
Open Office 3.4.1 on Windows 7
Re: [Solved] Simple timesheet
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.
- Attachments
-
- simple_timesheet2.ods
- (37.21 KiB) Downloaded 236 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice