Calculate Hours and overtime by week

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gregt812 - 23 Nov 2005 18:04 GMT

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 |

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

Signature

gregt812

Reply to this Message

DOR - 23 Nov 2005 19:03 GMT

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

> I am trying to calculate employees pay with overtime starting after 40
> hours per week.
[quoted text clipped - 43 lines]
> gregt812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17539
> View this thread: http://www.excelforum.com/showthread.php?threadid=487714

Reply to this Message