Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Complex Timesheet
Hello all, I am new to this forum and have been searching for something like the information I need and have not found it yet. Basically I need a spreadsheet that will calculate hour work (I want to take human error out of the equation) The rules for this sheet are as follows
1. The first 8 hours of the day are either regular 1st or regular 2nd shift (any hour from 1800-0600 as long as they are part of the first 8 hours of the shift) 2. The same as 1 applies to Sundays except hours will be Sunday 1st and Sunday 2nd. 3. Everything after the first 8 hours of the day is overtime. 4. Everything after the first 40 hours of (Regular1st +Regular 2nd +sunday 1st + sunday 2nd) is overtime. 5. Overtime is capped at 20 hours per week (40 hours per pay period) So no more than 60 hours per week (120 hours per pay period) I have attached an example to show what it would basically look like you will notice in the example that there are hours worked that are not totaled because they exceed the cap. Any Help with filling out the formulas would be greatly appreciated, honestly I am not even sure where to start. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Timesheet
Hi,
Am Sun, 28 Apr 2013 10:17:23 +0100 schrieb AAFES: 1. The first 8 hours of the day are either regular 1st or regular 2nd shift (any hour from 1800-0600 as long as they are part of the first 8 hours of the shift) 2. The same as 1 applies to Sundays except hours will be Sunday 1st and Sunday 2nd. 3. Everything after the first 8 hours of the day is overtime. 4. Everything after the first 40 hours of (Regular1st +Regular 2nd +sunday 1st + sunday 2nd) is overtime. 5. Overtime is capped at 20 hours per week (40 hours per pay period) So no more than 60 hours per week (120 hours per pay period) please have a look: https://skydrive.live.com/#cid=9378A...121822A3%21191 for the workbook "Timesheet_V2" I hope I understood your expectation Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Quote:
Last edited by AAFES : April 28th 13 at 01:17 PM Reason: Forgot Information |
#4
|
|||
|
|||
Not sure if anyone was able to fix the issues with the spreadsheet or not, or if there is even a way to do what I am asking, I am at a loss.
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Timesheet
Hi,
Am Tue, 30 Apr 2013 16:02:32 +0100 schrieb AAFES: Not sure if anyone was able to fix the issues with the spreadsheet or not, or if there is even a way to do what I am asking, I am at a loss. have a look: https://skydrive.live.com/#cid=9378A...121822A3%21191 for the workbook "Timesheet_V2" The rest of the work is for you. I don't know what you will do with Thursday. In your sheet the work time is 11 hours but you write 8 hours in OT. I think you can modify it to suit. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
|
|||
|
|||
Quote:
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Timesheet
Hi,
Am Wed, 1 May 2013 01:49:29 +0100 schrieb AAFES: Dankeschön, meine Frau ist auch Deutsche. This helps out a lot thanks. please look again for your workbook. The formula for Regular2 and Sun2 must be changed. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
|
|||
|
|||
Quote:
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Timesheet
Hi,
Am Wed, 1 May 2013 14:51:02 +0100 schrieb AAFES: I have been looking at it for most of the day, and still can't quite figure out exactly how the formulas need to change to get it to calculate correctly. I will probably continue to try and work on it. in the new version, the formulas are already changed. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
|
|||
|
|||
Quote:
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Timesheet
On Thu, 2 May 2013 03:00:16 +0100, AAFES
wrote: Claus Busch;1611539 Wrote: Hi, Am Wed, 1 May 2013 14:51:02 +0100 schrieb AAFES: - I have been looking at it for most of the day, and still can't quite figure out exactly how the formulas need to change to get it to calculate correctly. I will probably continue to try and work on it.- in the new version, the formulas are already changed. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 For the second shift hours, in the spreadsheet if you were to plug in a start time of 9:00 and a lunch from 14:00-15:00 and a leave time of 21:00 it will spit out 7 hours regular first, 1 hour regular 2nd and 3 hours OT it should read out 8 hours regular first, (9:00-18:00 minus a 1 hour lunch is 8 hours) and 3 hours OT. I have started working out the other issues I had with the spreadsheet and the Maximums. +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ If someone has to sit at a machine and have "a session" for entering their time, it is no harder to have them make more than one entry. So, if you make (force) morning period, a lunch period, and an afternoon period (use 'early' and 'late' period for other shifts), you will be able to segregate the lunch period. Since you said that they vary, this would be ideal. Then you could perform your math on these grouped subtotals and simplify the workbook's operations. So the person would have to enter their morning time and their lunch and their afternoon time. No big deal to make a couple additional entries while you are already sitting at the machine. I have a simplified time accrual only sheet which you may get ideas or styles or formatting from. Use as you wish. It does no OT calculations. Again, it is simply accrual based, and reports total hours for given jobs. Give it a look... Check out the time incrementing 'feature' for data entry use. http://www.mediafire.com/view/?8atcc048a4dfndt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Complex Timesheet Record | Excel Discussion (Misc queries) | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
Timesheet help | Excel Worksheet Functions | |||
Somewhat complex timesheet problem. | Excel Worksheet Functions | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) |