Over Time Calculation in Excel by taj12468

VIEWS: 34 PAGES: 21

Over Time Calculation in Excel document sample

More Info
									                                                         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

								
To top