[Solved] Simple timesheet

Discuss the spreadsheet application

[Solved] Simple timesheet

Postby Spaceboy64 » Mon Mar 23, 2020 12:41 am

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

Re: Simple timesheet

Postby FJCC » Mon Mar 23, 2020 1:05 am

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 11 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Simple timesheet

Postby Spaceboy64 » Mon Mar 23, 2020 2:29 am

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 9 times
Open Office 3.4.1 on Windows 7
Spaceboy64
 
Posts: 4
Joined: Mon Mar 23, 2020 12:15 am

Re: Simple timesheet

Postby FJCC » Mon Mar 23, 2020 3:08 am

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 12 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Simple timesheet

Postby Spaceboy64 » Mon Mar 23, 2020 5:35 am

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

Re: Simple timesheet

Postby Villeroy » Mon Mar 23, 2020 3:08 pm

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 18 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Simple timesheet

Postby Spaceboy64 » Thu Mar 26, 2020 11:34 pm

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

Re: [Solved] Simple timesheet

Postby Villeroy » Fri Mar 27, 2020 10:27 am

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 16 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests