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.

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

Click here for details about
obtaining this file

file: XL_PayEnh.htm

Page last updated: Mar13

CMIDX S4 P2 N