Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? +-------------------------------------------------------------------+ |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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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$21B24-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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to calculate overtime automatically on Excel timesheets | Excel Worksheet Functions | |||
How can I calculate overtime? | Excel Discussion (Misc queries) | |||
Could you help me to calculate overtime | Excel Worksheet Functions | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
need help w/formula for calculating overtime hours | Excel Worksheet Functions |