For small businesses who do not want to pay an outside service for payroll the first port of call is usually Microsoft Excel. Standard office type employees are pretty simple to keep track of, but what if your employees have overtime and other work structures? How do you calculate the correct pay?
Let’s take a look!
First download our example spreadsheet.
Instructions
- Fill the Rates Table.
- Regular – the rate for regular hour
- Nights & Weekend – the rate for nights and weekends
- Over Time – the rate for over time hours
- non over time hours – the number of “regular” rate hours, every additional hour will be calculated as over time
- Fill the Hours Table – for each day fill the number of working hours in day, night time or weekend.
- Salary table
- In the Salary table you will get the sum of regular weekend\nights and over time hours
- In the salary column you will see the salary calculation according to the rates in the Rates table
- Calculation – in this table you can see each day over time calculation
How does it work?
There is a lot going on in this spreadsheet but in actual fact it is relatively straightforward. Let’s start from the end.
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
Now we have to calculate the number of working hours in the day, nights, weekends and over-time.
First we add 4 columns for each day: day, OT (day), night OT(night), in these columns we will write the hours that will be used in the salary calculation.
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 OT and night hours OT on Monday is 0:
Let’s look at different numbers:
In this case there are no OT hours on Monday morning, but since Monday day + Monday night = 22 , for the salary calculation we will count 10 hours at night and 2 hours as OT.
Let’s go over the formulas of Monday and Tuesday:
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 Day)
if the number of hours on Monday Day exceed the non OT hours number, then on the salary calculation we count only non over time hours as regular hours (the rest will be calculated 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 calculating the salary.
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 hours in Monday day didn’t exceed the OT limit and therefore the number of OT hours for Monday Day is 0. If not then the number of OT hours will be the difference between Monday day and calculated Monday day.
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 >=Non_OT_Hours,Non_OT_Hours-Monday day, Monday Night))
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 this case the night hours will be 0. otherwise we have to check 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) ( we want to count only the hours that doesn’t exceed the OT 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 night hours.
Monday night OT :
(P8=C8,0,C8-P8)
Same as Monday day OT
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 OT,O8)>0,0,IF(SUM(Monday day ,Monday night, Tuesday day)>Non_OT_Hours,Non_OT_Hours-SUM(Monday day, Monday night),Tuesday day))
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, if there were, then the calculated Tuesday day will be 0 (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. Tuesday day hours will be (non over time hours – Monday day – Monday night) otherwise, if we didn’t exceed the over time limit. The calculated Tuesday day will be equal to Tuesday day
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
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 night OT,Tuesday day OT)>0,0,IF(SUM(Monday day , Monday night, Tuesday day , Tuesday night)>=Non_OT_Hours,Non_OT_Hours-SUM(Monday day , Monday night , Tuesday Day),Tuesday Night))
Same as Monday night : if we already have OT hours then calculated Tuesday night 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 limit, if it does, the calculated Tuesday night will be (non over 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 Night will be equal to Tuesday night.
Tuesday Night OT :
(T8=E8,0,E8-T8) -> (Calculated Tuesday night= Tuesday Night,0,Tuesday Night-Calculated Tuesday night)
Same as Monday day OT
Summary
This seems like a lot but providing you have the information there it is just a case of working through step by step. Take a look at our example spreadsheet and see how you can modify it for your own needs!
About the author
Yoav Ezer is the CEO of a company that produces PDF to XLS conversion software. He also co-authors the technology and productivity blog Codswallop.
For more Excel tips from Yoav, join him on Facebook or Twitter.
Popularity: 7% [?]
Post a Comment