|Click here to view a new replacement page 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.
|Basic Pay||0%||£10 per hour|
|Nights (8pm to 7am)||30%||£13|
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.
Would you like to learn more?
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] =B4*24*12||convert the excel time value into a 5 minute time unit|
|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'.
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.
|[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
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:
Formulas used in the example
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||© meadinkent.co.uk 2011||CMIDX S4 P2B N|