|| Links | Alphabetical Index | Excel Times | Excel Web Application ||
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(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.
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||© MeadInKent.co.uk 2016||CMIDX S4 P2 N|