[Solved] Simple timesheet

Discuss the spreadsheet application
Locked
Spaceboy64
Posts: 4
Joined: Mon Mar 23, 2020 12:15 am

[Solved] Simple timesheet

Post by Spaceboy64 »

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
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Simple timesheet

Post by FJCC »

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.
Spaceboy64
Posts: 4
Joined: Mon Mar 23, 2020 12:15 am

Re: Simple timesheet

Post by Spaceboy64 »

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!
Attachments
TimeSheet_fjcc2.ods
(16.09 KiB) Downloaded 143 times
Open Office 3.4.1 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Simple timesheet

Post by FJCC »

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.
Spaceboy64
Posts: 4
Joined: Mon Mar 23, 2020 12:15 am

Re: Simple timesheet

Post by Spaceboy64 »

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.
Open Office 3.4.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Simple timesheet

Post by Villeroy »

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.
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
Spaceboy64
Posts: 4
Joined: Mon Mar 23, 2020 12:15 am

Re: Simple timesheet

Post by Spaceboy64 »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Simple timesheet

Post by Villeroy »

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
Locked