Complex Timesheet

[AAFES]

Hello all, I am new to this forum and have been searching for somethin
like the information I need and have not found it yet. Basically I nee
a spreadsheet that will calculate hour work (I want to take human erro
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 2n
shift (any hour from 1800-0600 as long as they are part of the first
hours of the shift)
2. The same as 1 applies to Sundays except hours will be Sunday 1st an
Sunday 2nd.
3. Everything after the first 8 hours of the day is overtime.
4. Everything after the first 40 hours of (Regular1st +Regular 2n
+sunday 1st + sunday 2nd) is overtime.
5. Overtime is capped at 20 hours per week (40 hours per pay period) S
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 yo
will notice in the example that there are hours worked that are no
totaled because they exceed the cap. Any Help with filling out th
formulas would be greatly appreciated, honestly I am not even sure wher
to start

+-------------------------------------------------------------------
|Filename: Timesheet.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=858
+-------------------------------------------------------------------

 

[Claus Busch]

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=9378AAB6121822A3&id=9378AAB6121822A3!191
for the workbook "Timesheet_V2"
I hope I understood your expectation


Regards
Claus Busch

 

[AAFES]

Hi

Am Sun, 28 Apr 2013 10:17:23 +0100 schrieb AAFES


please have a look
http://tinyurl.com/3s4zwa
for the workbook "Timesheet_V2
I hope I understood your expectatio


Regard
Claus Busc

That is basically perfect however I forgot to mention that the Lunc
breaks and the Dinner breaks need to be taken out, they are not alway
an hour they could be shorter or longer. Also, basically the way i
should be for the TOTALS row should be Regular 1st + Regular 2nd = 64
Sunday 1st + Sunday 2nd = 16 and OT= 40 this is the MAX if Sundays ar
worked, if Sundays are not worked the MAX in the TOTALS row would b
Regular 1st+ Regular 2nd= 80 and OT=40 if there is any way to edit thi
sheet to reflect that it would be perfect. Also you are very fas
Kuddos on the awesome response time. I also forgot to mention that afte
the first 40 hours (regular 1st+regular 2nd+Sunday 1st +Sunday 2nd) o
just (regular 1st + regular 2nd) the rest of the time worked would b
overtime up to the max of 20 hours per week. so if on Wednesday th
individual has met their 40 hours of (regular 1st+regular 2nd+Sunda
1st +Sunday 2nd) or just (regular 1st + regular 2nd) the Thursday an
hours worked would be only OT hours up to the max of 20 hours (som
people will work hours that they do not get paid for) this is a deploye
environment, we are in Afghanistan

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[AAFES]

Not sure if anyone was able to fix the issues with the spreadsheet o
not, or if there is even a way to do what I am asking, I am at a loss

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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=9378AAB6121822A3&id=9378AAB6121822A3!191
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

 

[AAFES]

Hi,

Am Tue, 30 Apr 2013 16:02:32 +0100 schrieb AAFES:
-
loss.-

have a look:
http://tinyurl.com/3s4zwaz
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

Dankeschön, meine Frau ist auch Deutsche. This helps out a lot thanks

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[AAFES]

Hi,

Am Wed, 1 May 2013 01:49:29 +0100 schrieb AAFES:
-
thanks.-

please look again for your workbook. The formula for Regular2 and Sun2
must be changed.


Regards
Claus Busch

I have been looking at it for most of the day, and still can't quit
figure out exactly how the formulas need to change to get it t
calculate correctly. I will probably continue to try and work on it

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[AAFES]

Hi,

Am Wed, 1 May 2013 14:51:02 +0100 schrieb AAFES:
-

in the new version, the formulas are already changed.


Regards
Claus Busch

For the second shift hours, in the spreadsheet if you were to plug in
start time of 9:00 and a lunch from 14:00-15:00 and a leave time o
21:00 it will spit out 7 hours regular first, 1 hour regular 2nd and
hours OT it should read out 8 hours regular first, (9:00-18:00 minus
1 hour lunch is 8 hours) and 3 hours OT. I have started working out th
other issues I had with the spreadsheet and the Maximums

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[CellShocked]

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