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. The IF function tests to see if the night shift was worked. Now this formula may seem quite daunting, so let’s break it down a little. The formula below can be entered into cell H5 of the Excel timesheet to achieve this. The final formula will need to look up the rate using the number of the weekday in the correct table. =WEEKDAY(A5,2) Looking up the Correct Rate of Pay The formula below returns the day of the week that they started work as a number, where Monday is 1 and Sunday is 7. To do this we can use the WEEKDAY function. 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. The rate for starting work on a Saturday or Sunday is higher than the other days 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. Range Names have been assigned to each table. The second column stores the rate of pay. The first column is the day of the week represented as a number, where 1 is Monday and 7 is Sunday. One for day shifts and the other for nights. To include this, 2 lookup tables were set up on a different sheet. In addition to the type of shift worked, the day of the week if also a factor in determining the rate of pay applied. The IF function first tests if it was a night shift, then test to see if it was worked overnight (A5 calculate hours in excel template

The formula below is entered into column F to calculate hours worked on the day shift. 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. Now in this timesheet it is not quite that straight forward. If the shift is worked overnight, so you started work on one day but finished the next, then the formula below is used. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours. To do this the following formula can be used. Our first task is to calculate the number of hours worked on the shift. A column (column E) is also used to record whether it is a day or night shift.

This will make it easier for our calculations. In this timesheet, the day and night shifts are entered on separate rows. Excel Timesheet with Different Rates for Shift Work Calculating Hours Worked

Calculate hours in excel template