Calculate Hours and overtime by week

[gregt812]

I am trying to calculate employees pay with overtime starting after 40
hours per week.
Below is an example for two employees over the two week pay period:

Date In Time In Date Out Time Out Total Time
E555555555N
D1107191811080036R 11/07/05 19:18 11/08/05 0:36 5.30
D1109182411100015R 11/09/05 18:24 11/10/05 0:15 5.85
D1112122611121716R 11/12/05 12:26 11/12/05 17:16 4.83
D1114120111141711R 11/14/05 12:01 11/14/05 17:11 5.17
D1115170111160043R 11/15/05 17:01 11/16/05 0:43 7.70
D1118115311181707R 11/18/05 11:53 11/18/05 17:07 5.23
D1119121711191703R 11/19/05 12:17 11/19/05 17:03 4.77
E333333333N
D1107124111080036R 11/07/05 12:41 11/08/05 0:36 11.92
D1110065811101702R 11/10/05 6:58 11/10/05 17:02 10.07
D1111065911111958R 11/11/05 6:59 11/11/05 19:58 12.98
D1112080711121701R 11/12/05 8:07 11/12/05 17:01 8.90
D1113065811131604R 11/13/05 6:58 11/13/05 16:04 9.10
D1113160411131658R 11/13/05 16:04 11/13/05 16:58 0.90
D1114170611150045R 11/14/05 17:06 11/15/05 0:45 7.65
D1115171011160043R 11/15/05 17:10 11/16/05 0:43 7.55
D1118085911181733R 11/18/05 8:59 11/18/05 17:33 8.57
D1118195011182100R 11/18/05 19:50 11/18/05 21:00 1.17
D1119085911191609R 11/19/05 8:59 11/19/05 16:09 7.17
D1120090011201700R 11/20/05 9:00 11/20/05 17:00 8.00


The first column is the raw data that is given to. I have broken out
the data into a readable format and calculated time for each day. I
need to find total time for each of the two weeks so I can calculate
overtime (if any) then calculate total time per employee for both weeks
and overtime.
The pay first week started on 11/07/05 and ended on 11/13/05. The
second week started on 11/14/2005 and ended on 11/20/05.
I attached the above as a .doc file to make copy/paste easier.
Any suggestions?


+-------------------------------------------------------------------+
|Filename: payroll.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4060 |
+-------------------------------------------------------------------+

 

[DOR]

Put end of week date in B24 (11/13) and B25 (11/20) and in F24 put

=SUMPRODUCT(--($B$2:$B$21>B24-7),--($B$2:$B$21<=B24),$F$2:$F$21)

drag down for each week.

assuming data in rows 2 to 21. This will give you total time from
column F for each week.

This also assumes that all time worked continuously is attributed to
the day on which the shift started. So, if a person starts at 23:00 on
11/13 and works into the next day, which is the first day of a new
week, his or her time time on 11/14 is deemed to be part of the
previous week. If you want it to be otherwise, things get a bit more
complex.

If you want column B to have the week starting dates (11/7 and 11/14),
then the formula is:

=SUMPRODUCT(--($B$2:$B$21>=B24),--($B$2:$B$21<B24+7),$F$2:$F$21)

HTH

Declan O'R