Home Excel Access | Feedback | Date and Time functions | Text() to format values |

Using Excel to calculate enhanced rates for shifts

 Click here to view a new replacement page which has been added with a slightly different methodology

In organisations which work around the clock, some staff may be paid additional amounts for working unsocial hours at nights and weekends. Companies are likely to have payroll systems to calculate these enhanced rates, but it may also be useful to have a financial model to predict these pay costs for planning purposes.

This section will look at a worksheet model and also a macro to calculate the costs of a shift system. If staff all work regular shifts (e.g. there are 3 x 8 hour shifts every day starting at 6am, 2pm and 10pm) the costing may be simple but if the start times and lengths of shifts vary, the calculations can be complicated.

 Description Enhancement Example Basic Pay 0% £10 per hour Nights (8pm to 7am) 30% £13 Saturdays 30% £13 Sundays 60% £16

The table (right) shows the enhancements to pay that staff will receive for working unsocial hours. In order to create a model that reflects local conditions, you may need to consider which rates are paid when shifts overlap categories (such as Sunday night / Monday morning). It may also require further modification for special days such as public holidays.

Excel can recognise values entered into cells as times and will perform calculations on them. Calculating the length of a shift is simply a matter of subtracting one time from another. However if the shift times pass over midnight there is a problem. If a nighttime shift starts at 9pm (21:00) and finishes at 8:30am (08:30) the result is negative and Excel cannot cope with a time of minus 11.5 hours. This can be resolved by adding one to the finish time if the finish time is less than the start time: i.e. =IF(finish_t<start_t,finish_t+1-start_t,finish_t-start_t)

The following series of functions are all applied against a single set of time values in order to calculate a single enhancement. The method would therefore not be practicable for a range of values. If shift enhancements need to be calculated for a range of cells, it is probably necessary to create a user defined function using visual basic.

 [C4] =B4*24*12 convert the excel time value into a 5 minute time unit [D5] =IF(C5

In this model the times have been converted into '5 minute time units' - i.e. 00:05 = 1, 00:10 = 2 .... 24:00 = 288. These integers are easier to use than the times in their original formats of decimal fractions. If a shift continues into the next day, the finish time is increased by 288. Cells D4 and D5 are named as 'StUnits' and 'FiUnits'.

The times at which the enhanced rates are payable must be defined and also converted into 5 minute units. This is done for two days in case the shift runs overnight.

The formula to calculate the periods of the shift which fall into particular time slots (i.e. periods of enhanced rates or plain time) is complicated and must test for four separate groups of conditions. In this example this has been done by splitting these tests into two separate formulas.

 [J19] =IF(AND(stUnits>G11, stUnitsG11, stUnits=G12), G12-stUnits, 0) if the shift starts during the time slot and ends either during it or after it ... [K19] =IF(AND(stUnits=G11), fiUnits-G11, 0)+IF(AND(stUnitsG12), G12-G11, 0) if the shift starts before the current time slot and ends either during it or after it ... [L19] =SUM(J19:K19) add together both of the time units [M19] 1.3 the enhancement paid for time worked during the current slot or period (+ 30%) [N19] =M19*L19 the time units uplifted by any enhanced rates [N25] =N24/L24 weighted time units divided by the worked time units to return a percentage enhancement

These calculations can be consolidated into fewer, more complex formulas, but if you wish to calculate the enhancement for a list of shift times, it is better to create a User Defined function.

The following VBA code has the same purpose of calculating the enhancements paid for a shift, but it also incorporates special rates paid for Saturdays and Sundays.

 ```Function ShiftEnhancements(ShiftStartDate As Date, StartTime As Date, EndTime As Date) As Single ' revised 10Aug06 Dim stUnits As Integer, EndUnits As Integer, Val2 As Single Dim Night_Enh(4) As Single, ShiftUnits(5) As Integer, MyWeekDay As Integer, n As Integer MyWeekDay = Weekday(ShiftStartDate) stUnits = StartTime * 24 * 12 ' convert times into 5 minute units EndUnits = EndTime * 24 * 12 If EndUnits < stUnits Then EndUnits = EndUnits + 288 ' account for overnight shifts If EndUnits = stUnits Then ShiftEnhancements = 1 Exit Function End If If MyWeekDay = 1 Then 'Sun Night_Enh(1) = 1.6 ' 00:00 Night_Enh(2) = 1.6 ' 07:00 Night_Enh(3) = 1.6 ' 20:00 Night_Enh(4) = 1 ' 07:00 day 2 End If If MyWeekDay > 1 And MyWeekDay < 6 Then 'Mon-Thu Night_Enh(1) = 1.3 Night_Enh(2) = 1 Night_Enh(3) = 1.3 Night_Enh(4) = 1 End If If MyWeekDay = 6 Then 'Fri Night_Enh(1) = 1.3 Night_Enh(2) = 1 Night_Enh(3) = 1.3 Night_Enh(4) = 1.3 End If If MyWeekDay = 7 Then 'Sat Night_Enh(1) = 1.3 Night_Enh(2) = 1.3 Night_Enh(3) = 1.3 Night_Enh(4) = 1.6 End If 'ShiftUnits(1) = 0 ' 00:00 ShiftUnits(1) = 84 ' 07:00 ShiftUnits(2) = 240 ' 20:00 ShiftUnits(3) = 372 ' 07:00 day 2 ShiftUnits(4) = 528 ' 20:00 Val2 = 0 For n = stUnits + 1 To EndUnits If n <= ShiftUnits(1) Then  Val2 = Val2 + Night_Enh(1) End If If n <= ShiftUnits(2) And n > ShiftUnits(1) Then  Val2 = Val2 + Night_Enh(2) End If If n <= ShiftUnits(3) And n > ShiftUnits(2) Then  Val2 = Val2 + Night_Enh(3) End If If n <= ShiftUnits(4) And n > ShiftUnits(3) Then  Val2 = Val2 + Night_Enh(4) End If Next ShiftEnhancements = Val2 / (EndUnits - stUnits) End Function ```

If this code is copied and pasted into a VB Module it can be called upon in a worksheet using the custom function ShiftEnhancements(). Note that it returns the percentage enhancement that can be applied to the worked hours, not the enhanced hours. Examples of it's output are shown below:

 Formulas used in the example [G22] =IF(F22