MeadInKent
| Links | Alphabetical Index | Excel Times | Excel Web Application |

Use Excel to calculate enhanced pay rates

Some employees may be paid enhanced rates of pay for working unsocial hours. If they work between certain night time hours, their rates of pay may be uplifted by an agreed multiple. Calculating the enhancement for irregular shift lengths or for 'out of hours' call outs can be difficult.

Excel table of shift start time and end time

The following Excel tables will calculate the number of hours to be paid, based on up to four different bands and enhancement rates. For example someone working between midnight and 5:30am (table row 9) will be paid double their basic rate (cell F9).

A workers shift times can be entered in cells C4:C5. The times are shown as decimals in column D and the second value is adjusted if it passes beyond midnight. [D5]=IF(C5<C4, 1+C5, C5)

  Click to Open and Edit this spreadsheet as an Excel Web Application

A table can be created defining the bands of times for each enhancement rate. The four time bands are replicated for the following day in case the shift continues after midnight ([B13]=B9+1). The length of time worked within each banding is calculated. The weighted times in the final column represent the hours multiplied by the enhancement for each time band. Therefore column K creates a weighted average of hours to be paid.

An Excel table with enhanced pay rates for shift times

The complicated formulas are in columns G and H. Cells D4 and D5 have been named as StartTime and EndTime.

start [G9]=IF(AND(StartTime<D9, EndTime>D9), D9, IF(AND(StartTime>=D9, StartTime<E9), StartTime, E9))
finish [H9]=IF(AND(EndTime>D9, EndTime<E9), EndTime, E9)

If you have long lists of staff requiring payroll calculations, this table is not a practical solution. In that situation a visual basic user defined function is required.

The following code creates a custom Visual Basic function which produces the same result as the table above. The PayEnh(StartTime,EndTime) function has been customised to use the same fixed time bandings and enhancement rates. In the example (right) the enhanced hours have been calculated using the function: [K24]=payenh(TEXT(C4, "hh:mm"), TEXT(C5, "hh:mm"))

Note that the two parameters are defined as text variables. This enables the program code to be used in an Access database or where it is reading a list of report data.

In this example it returns the result 0.51042 which is the decimal equivalent of 12:15 (12 hours, 15 minutes). The paid hours are 63% higher than the worked hours.

Function PayEnh(ShiftFrom As String, ShiftTo As String) As Double
' returns an enhanced number of hours for a shift
' [MeadInKent 2013] may not be suitable for real payroll calculations

Dim tFrom(7) As Double, tTo(7) As Double, n As Integer
Dim StartTime As Double, EndTime As Double, EnhRate(7) As Double
Dim BandStart As Double, BandFinish As Double, CumTime As Double

StartTime = Round(TimeValue(ShiftFrom), 5)
EndTime = Round(TimeValue(ShiftTo), 5)
If EndTime < StartTime Then EndTime = EndTime + 1 ' shifts which extend beyond midnight
'Debug.Print StartTime, EndTime

' define the times (as decimals with 5dp) for enhanced hours

tFrom(0) = 0
tTo(0) = 0.22917
EnhRate(0) = 2

tFrom(1) = 0.22917
tTo(1) = 0.5
EnhRate(1) = 1.25

tFrom(2) = 0.5
tTo(2) = 0.83333
EnhRate(2) = 1.25

tFrom(3) = 0.83333
tTo(3) = 1
EnhRate(3) = 1.5

' add additional bandings for shift times which extend to the following day
For n = 0 To 3
 tFrom(n + 4) = tFrom(n) + 1
 tTo(n + 4) = tTo(n) + 1
 EnhRate(n + 4) = EnhRate(n)
Next n

For n = 0 To 7 ' sum the hours within each banding (tFrom to tTo)

If StartTime < tFrom(n) And EndTime > tFrom(n) Then
 BandStart = tFrom(n)
Else
 If StartTime >= tFrom(n) And StartTime < tTo(n) Then
  BandStart = StartTime
 Else
  BandStart = tTo(n)
 End If
End If

If EndTime > tFrom(n) And EndTime < tTo(n) Then
 BandFinish = EndTime
Else
 BandFinish = tTo(n)
End If

CumTime = CumTime + ((BandFinish - BandStart) * EnhRate(n))
'Debug.Print n, BandStart, BandFinish, CumTime
Next
PayEnh = CumTime

End Function

Note that this macro and spreadsheet example is shown for illustrating a possible process.
It is not suitable for applying to a real payroll process without modification and thorough testing.

There is also an older and slightly different version of this page which may be of interest.

This is a New Page and is NOT currently
included in the Excel Functions guide
Document is in PDF format Click here for details about
obtaining this file

file: XL_PayEnh.htm Page last updated: Mar13 Open MeadInKent Facebook page MeadInKent.co.uk 2016 CMIDX S4 P2 N