When creating a timesheet in Excel you may need to calculate different rates of pay. This could be because of overtime, or the type of work being done.
In this tutorial, we create a timesheet that uses different rates of pay for working weekends, and also night shifts.
Calculating Hours Worked
In this timesheet, the day and night shifts are entered on separate rows. This will make it easier for our calculations. A column (column E) is also used to record whether it is a day or night shift.
Our first task is to calculate the number of hours worked on the shift. To do this the following formula can be used. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours.
(D5-B5)*24
If the shift is worked overnight, so you started work on one day but finished the next, then the formula below is used.
(1+D5-B5)*24
Now in this timesheet it is not quite that straight forward. We need to test first what shift they are doing and whether it was worked overnight or not, so that the correct formula can be run.
The formula below is entered into column F to calculate hours worked on the day shift.
=IF(E5="Day",(D5-B5)*24,0)
In column G, the formula below was used to calculate hours worked on the night shift. The IF function first tests if it was a night shift, then test to see if it was worked overnight (A5<C5), and runs the correct calculation as a result.
=IF(E5="Night",IF(A5<C5,(1+D5-B5)*24,(D5-B5)*24),0)
Setting up the Lookup Tables in the Excel Timesheet
In addition to the type of shift worked, the day of the week if also a factor in determining the rate of pay applied.
To include this, 2 lookup tables were set up on a different sheet. One for day shifts and the other for nights.
The first column is the day of the week represented as a number, where 1 is Monday and 7 is Sunday. The second column stores the rate of pay.
Range Names have been assigned to each table. They have been named day and night.
Calculating the Day of the Week
In the day and night lookup tables, the rate of pay is determined by the day of the week that they worked. The rate for starting work on a Saturday or Sunday is higher than the other days of the week.
So before we can look up the rate we need to know how to calculate the day of the week that they started their shift. To do this we can use the WEEKDAY function.
The formula below returns the day of the week that they started work as a number, where Monday is 1 and Sunday is 7.
=WEEKDAY(A5,2)
Looking up the Correct Rate of Pay
With all this information to hand. The final formula will need to look up the rate using the number of the weekday in the correct table.
The formula below can be entered into cell H5 of the Excel timesheet to achieve this.
=IF(E5="Night",G5*VLOOKUP(WEEKDAY(A5,2),night,2,FALSE),F5*VLOOKUP(WEEKDAY(A5,2),day,2,FALSE))
Now this formula may seem quite daunting, so let’s break it down a little.
The IF function tests to see if the night shift was worked. If so a VLOOKUP function is used to look up the required rate of pay in the night table, and this is then multiplied by the hours in cell G5.
If they did not work the night shift, then a VLOOKUP function returns the rate from the day table and this is then multiplied by the hours in cell F5.
well appreciated I will be part of this training