MeadInKent
| 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.

Hours worked on a shift

[C4] =B4*24*12 convert the excel time value into a 5 minute time unit
[D5] =IF(C5<C4, C5+288, C5) if the end time is less than the start time (and therefore must be in the following day), add 288 x 5 minute time units (i.e. 24 hrs) to the end time. 
[B6] =D6/24/12 convert the adjusted length of shift, back from 5 minute time units into a normal excel time value

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'.

Times during a shift when enhancement rates change

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.

Use Excel to calculate extra pay for working overnight

[J19] =IF(AND(stUnits>G11, stUnits<G12, fiUnits<G12), fiUnits-stUnits, 0)+IF(AND(stUnits>G11, stUnits<G12, fiUnits>=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<G12, fiUnits>=G11), fiUnits-G11, 0)+IF(AND(stUnits<G11, fiUnits>G12), 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:


A user defined Excel function to calculate shift enhancements

Formulas used in the example

[G22] =IF(F22<E22,1+F22-E22,F22-E22)

[H22] =ShiftEnhancements(D22,E22,F22)

[I22] =H22*G22


Click here for other examples of basic Time and Date functions.

This page is not currently included
in the Excel functions guide
Click here for details about
obtaining this file

file: excel-shifts.htm meadinkent.co.uk 2016 CMIDX S4 P2B N