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] =B42412	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

CMIDX S4 P2B N