+ Reply to Thread
Results 1 to 2 of 2

Calculate Hours and overtime by week

  1. #1
    Registered User
    Join Date
    12-16-2004
    Posts
    5

    Calculate Hours and overtime by week

    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?
    Attached Files Attached Files

  2. #2
    DOR
    Guest

    Re: Calculate Hours and overtime by week

    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


    gregt812 wrote:
    > 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 |
    > +-------------------------------------------------------------------+
    >
    > --
    > gregt812
    > ------------------------------------------------------------------------
    > gregt812's Profile: http://www.excelforum.com/member.php...o&userid=17539
    > View this thread: http://www.excelforum.com/showthread...hreadid=487714



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1