 Home Excel Access | 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. 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. The complicated formulas are in columns G and H. Cells D4 and D5 have been named as StartTime and EndTime.

 start [G9]=IF(AND(StartTimeD9), D9, IF(AND(StartTime>=D9, StartTimeD9, EndTime

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 currentlyincluded in the Excel Functions guide Click here for details aboutobtaining this file

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