Forum Discussion

Grant de Jongh's avatar
Grant de Jongh
Copper Contributor
Jul 07, 2018

Calculate vairable payrates per single shift over midnight - Timesheet

Hi all,

I need to calculate a timesheet for agency ambulance work based on several critical elements, there are effectively eight pay rates between two types of work, event work, 999 work. For obvious reasons 999 work pays more. It generally is pretty easy until I do a night shift say from 9pm to 10am. Unsocial for example on 999 work kicks in at 10pm and ends at 6am, so I have 8hrs unsocial and 5 hours social if I finish on time. Week ends have different rates too.

Here are the hourly pay rate variables

Hourly pay rate                        EVENT       999

SOCIAL PAY RATE                   £12.00       £15.50
UNSOCIAL PAY RATE              £17.00       £15.50
SOCIAL PAY RATE W/E             £12.00      £16.00
UNSOCIAL PAY RATE W/E       £17.00       £17.00
SOCIAL HRS START TIME        06:00:00    06:00:00
UNSOCIAL HRS START            00:00:00     22:00:00
                                 

I have spent a lot of time on this and got pretty far (with a messy sheet 2!) the biggest challenge is if the shift goes over midnight [which is easy to calculate if straight forward i.e. (y-x+(y<x))*24] but splitting the social and unsocial then marrying up the two unsocial values i.e. 9pm-10pm and the 6am-10am hrs. Additionally when I start a shift after unsocial hours my values go crazy. SO if any of this makes sense PLEASE Help! Thanks.

excel
Formulas & Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 09, 2018

    Hi Grant,

     

    I'd suggest to add helper sheet like this

    where we have all combinations of hours for this and next day (totally 48) with 1 if an hour for that shift type ans zero otherwise. In addition, numbers for weekend are multiplied proportionally to rated. Here

    WDD if this and next days are working ones;

    WDE - work day and next weekend;

    WED - weekend and next working one;

    and it looks like i missed WEE

     

    Result will be like this

    I didn't combine all formulas in one (for total). Formula for pay unsocial will be

    =(SUMPRODUCT((Scale!$A$11:$A$58>C2*24)*(Scale!$A$11:$A$58<=E2*24)*OFFSET(Scale!$A$10,1,MATCH(B2&" WDD",Scale!$B$10:$M$10,0)+6,48))+
MOD(C2,1/24)*INDEX(OFFSET(Scale!$A$10,1,MATCH(B2&" WDD",Scale!$B$10:$M$10,0)+6,48),C2*24)*24+
MOD(E2,1/24)*INDEX(OFFSET(Scale!$A$10,1,MATCH(B2&" WDD",Scale!$B$10:$M$10,0)+6,48),E2*24)*24)*
IF(B2="Event",Scale!$B$3,Scale!$C$3)

    all others are similar ones.

    That's just mockup, I didn't fully tested it. Attached.

    • Grant de Jongh's avatar
      Grant de Jongh
      Copper Contributor
      Jul 17, 2018
      Thanks Sergei! I will have a play. I really appreciate your help

Resources