VIEWS: 34 PAGES: 21 CATEGORY: Business POSTED ON: 3/8/2011
Over Time Calculation in Excel document sample
Hours Monday Tuesday Wednesday Thursday Name Day Night Day Night Day Night Day Night Dan 20 3 7 7 4 2 4 9 Ron 4 7 4 10 3 6 4 10 David 4 2 4 0 3 2 14 3 Mike 3 4 1 4 2 3 2 6 Goerge 4 2 5 1 12 9 0 2 ours Friday Saturday Sunday Monay Day Night Weekend Weekend Day OT Night 2 4 5 6 20 0 3 2 4 5 9 4 0 7 2 5 5 9 4 0 2 8 7 4 5 3 0 4 3 0 5 6 4 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Calculation Monay Tuesday Wednesday OT Day OT Night OT Day OT Night OT 0 7 0 7 0 3 1 0 2 0 4 0 10 0 3 0 6 0 0 4 0 0 0 3 0 2 0 0 1 0 4 0 2 0 3 0 0 5 0 1 0 12 0 9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Calculation Thursday Friday Saturday Day OT Night OT Day OT Night OT Weekend 0 4 0 9 0 2 0 4 0 4 0 2 8 0 2 0 4 0 14 0 3 0 2 0 5 0 1 2 0 6 0 8 0 7 0 0 0 0 2 0 3 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Rates Regular Night&Weekend OverTime 10 15 20 Salary Saturday Sunday Regular Nights&Weekends OverTime OT Weekend OT 5 0 6 30 10 33 5 0 9 15 25 28 4 0 9 27 13 13 4 0 5 16 24 9 3 0 6 24 16 9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ates non over time hours 40 alary Salary 1110 1085 725 700 660 0 0 0 0 0 0 0 0 ` 0 0 0 0 Instructions 1. Fill the Rates Table. 1.1 Regular - the rate for regular hour 1.2 Nights & Weekend - the rate for nights and weekends 1.3 Over Time - the rate for over time hours 1.4 non over time hours - the number of "regular" rate hours, every extra hour will be calculated as over time 2. Fill the Hours Table 2.1 for each day fill the number of working hours in day time , night time or weekend. 3. Salary table 3.1 In the Salary table you will get the sum of regular weekend\nights and over time hours 3.2 In the salary column you will see the salary calculation according to the rates in the Rates table 4. Caclulation 4.1 in this table you can see each day over time calculation lated as over time How does it work? Lets start from the end: 1. Imagine you already have the number of regular hours night hours and overtime hours, calculating the salary is easy: salary = #regular hours *regular rate + #night weekend hours * night\weekend rate + # over time hours * over time rate 2. OK, now we have to calculate the number of working hours in the day ,nights \ weekends and over time: 2.1 Lets take it step by step, first we add 4 columns for each day: day ,OT (day), night, OT(night), in these columns we will write the Lets assume that the non over time hours is 20, in this case, since the employee didn’t accede the non over time hours, his regular Hours Calculation Monday Monday Day Night Day OT Night OT 10 5 10 0 5 0 Lets look at different numbers : Hours Calculation Monday Monday Day Night Day OT Night OT 10 12 10 0 10 2 In this case there are no OT hours on Monday morning , but since Monday day + Monday night = 22 , for the salary calculation we 3. Lets go over the formulas of Monday and Tuesday 3.1 Monday Day: IF(B8>=Non_OT_Hours_Rate,Non_OT_Hours_Rate,B8) -> if(Monday Day > Non_OT_Hours_Rate,Non_OT_Hours_Rate,Monday Da if the number of hours on Monday Day exceed the non OT hours number, then on the salary calculation we will count only #non ov 3.2 Monday Day OT: IF(N8=B8,0,B8-N8) - > IF (calculated Monday Day = Monday Day, 0, Monday Day - calculated Monday Day) if the number we calculated for Monday Day equals the number of hours in Monday Day it means that the number of working hou 3.3 Monday night : IF(O8>0,0,IF(SUM(B8:C8)>=Non_OT_Hours,Non_OT_Hours-B8,C8))-> IF(Monday Day OT>0,0,IF(Monday day + Monday night >=No if Monday day OT is > 0 it means that we already passed the OT limit and therefore every extra hour will be calculated as OT. In thi 3.4 Monday night OT : (P8=C8,0,C8-P8) same as 3.2 Monday day OT 3.5 Tuesday Day: IF(SUM(Q8,O8)>0,0,IF(SUM(B8:D8)>Non_OT_Hours,Non_OT_Hours-SUM(B8:C8),D8)) -> IF(SUM(Monday day OT , Monday night O sum(a,b) is a short way to write : or(a>0,b>0) , on Tuesday day calculation we first check if there were no over time hours until now 3.6 Tuesday Day OT : IF(R8=D8,0,D8-R8) - > IF(Calculated Tuesday day = Tuesday day ,0,Teusday day - calculated Tuesday day) same as 3.2 Monday day OT 3.7 Tuesday Night : IF(SUM(O8,Q8,S8)>0,0,IF(SUM(B8:E8)>=Non_OT_Hours,Non_OT_Hours-SUM(B8:D8),E8)) - > IF(SUM(Monday day OT,Monday nigh same as 3.4 Monday night : if we already have OT hours then calculated Tuesday night will be 0 (this hours will be added to the OT 3.8 Tuesday Night OT : (T8=E8,0,E8-T8) -> (Calculated Tuesday night= Tuesday Night,0,Tuesday Night-Calculated Tuesday night) same as 3.2 Monday day OT ours, calculating the salary is easy: + # over time hours * over time rate ekends and over time: , OT(night), in these columns we will write the hours that will be used in the salary calculation. For instance: ’t accede the non over time hours, his regular hours OT and night hours OT on Monday is 0 : nday night = 22 , for the salary calculation we will count 10 hours at night and 2 hours as OT Hours_Rate,Non_OT_Hours_Rate,Monday Day) e salary calculation we will count only #non over time hours as regular hours (the rest will be calculated as over time hours), if the number of hours do lculated Monday Day) Day it means that the number of working hours in Monday day didn’t exceed the OT limit and therefore the number of OT hours for Monday Day is 0. y OT>0,0,IF(Monday day + Monday night >=Non_OT_Hours,Non_OT_Hours-Monday day, Monday Night)) very extra hour will be calculated as OT. In this case the night hours will be 0. otherwise we have to check if the sum of Monday day and Monday night )) -> IF(SUM(Monday day OT , Monday night OT,O8)>0,0,IF(SUM(Monday day ,Monday night, Tuesday day)>Non_OT_Hours,Non_OT_Hours-SUM(Mon eck if there were no over time hours until now, if there were, then the calculated Tuesday day will be 0 (the hours will be counted as over time), if not culated Tuesday day) ,E8)) - > IF(SUM(Monday day OT,Monday night OT,Tuesday day OT)>0,0,IF(SUM(Monday day , Monday night, Tuesday day , Tuesday night)>=Non_OT_ ht will be 0 (this hours will be added to the OT hours), otherwise we check if the sum of the hours until Tuesday night exceed the non over time hours as over time hours), if the number of hours doesn’t exceed the non OT hours number then we can count all the hours as regular hours when calculatin the number of OT hours for Monday Day is 0. otherwise the number of OT hours will be the difference between Monday day and calculated Monday d k if the sum of Monday day and Monday night exceed the OT limit, if so, the calculated Monday night hours will be the (OT limit - Monday day hours) ( y)>Non_OT_Hours,Non_OT_Hours-SUM(Monday day, Monday night),Tuesday day)) the hours will be counted as over time), if not we check if the sum of the hours until Tuesday morning exceed the over time limit. if it does the calculate ight, Tuesday day , Tuesday night)>=Non_OT_Hours,Non_OT_Hours-SUM(Monday day , Monday night , Tuesday Day),Tuesday Night)) uesday night exceed the non over time hours limit, if it does, the calculated Tuesday night will be (non over time hours - Monday day - Monday night - all the hours as regular hours when calculating the salary. etween Monday day and calculated Monday day urs will be the (OT limit - Monday day hours) ( we want to count only the hours that doesn't exceed the OT limit). Last, if the sum of Monday day and M ceed the over time limit. if it does the calculated Tuesday day hours will be (non over time hours - Monday day - Monday night) ,otherwise, if we didn't Tuesday Day),Tuesday Night)) er time hours - Monday day - Monday night - Tuesday day). (the rest will be added to OT) , if not (we didn't pass the OT limit) , the calculated Tuesday T limit). Last, if the sum of Monday day and Monday night doesn't exceed the OT limit, the calculated Monday night hours will be equal to Monday nig y day - Monday night) ,otherwise, if we didn't exceed the over time limit. the calculated Tuesday day will be equal to Tuesday day n't pass the OT limit) , the calculated Tuesday Night will be equal to Tuesday night. onday night hours will be equal to Monday night hours. be equal to Tuesday day