Calculate total pay when shifts start and end on different days with differing pay rates across the shift

S

Sports123

New Member
Joined
Apr 12, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I am looking to calculate total pay for shifts which start and end on different days, with some days having different pay rates to others.

EG: shift start is 22:00 Friday, Shift end is 02:00 Saturday. Friday rate is x, saturday rate is y. So needs to calculate 2hrs at x and 2hrs at y to give total pay.

I have sheet one set up for start and end time input, with dates (and days) loaded from event info at the top.
Currrenly the 'Cost' column is pulling from a single base rate in sheet 2.

Sheet 2 contains the varying pay rates depending on role and day.

Any help being able to split the input of pay rates based on how long the shift lasts on the various days would be much appreciated!

Thanks

Costs - Working version.xlsx
ABCDEFGHIJK
1DateFriday, 16 April 2021
2Event
3Corporate Gates18:15
4Public Gates18:40
5Kick Off19:55
6Crowd Size10,001,15,000
7Zones Active6 Zones
8
9RoleNo. StaffDayDateStartEndShift HoursBreakHours EngagedCost
10Pre Clean 11Friday16/04/202110:0019:009:000:308:30$304.90
11Pre Clean 20Friday16/04/202110:0014:004:000:000:00$0.00
12Event Supervisor1Friday16/04/202113:0023:0010:000:309:30$340.77
13Event Staff 16Friday16/04/202117:4522:455:000:006:00$1,076.10
14Event Staff 20Friday16/04/202118:4522:454:000:000:00$0.00
15HTP10Friday16/04/202118:4522:454:000:0016:00$1,434.80
16Post Supervisor1Friday16/04/202122:303:305:000:005:00$179.35
17Post Staff40Friday16/04/202123:303:304:000:0016:00$5,739.20
18Total Cost$9,075.11
19Charge Out$11,813.05
20P/L$2,737.94
21
Sheet1
Cell Formulas
RangeFormula
C10:C17C10=TEXT($B$1,"dddd")
D10:D17D10=$B$1
G10:G17G10=IF(F10<E10,F10+1,F10)-E10
H10:H17H10=IF(G10>TIME(5,0,0),TIME(0,30,0),TIME(0,0,0))
I10:I17I10=B10*G10-(H10*B10)
J10:J11J10=(I10*24)*Sheet2!I3
J12J12=(I12*24)*Sheet2!I7
J13:J14,J16J13=(I13*24)*Sheet2!I3
J15J15=(I15*24)*Sheet2!I2
J17J17=(I17*24)*Sheet2!I5
J18J18=SUM(J10:J17)
J19J19=SUMPRODUCT((B6=Sheet2!A24:A30)*(B7=Sheet2!B23:D23)*Sheet2!B24:D30)
J20J20=J19-J18
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J14Cell Value<1textNO
J10:J17Cell Value<1textNO
J20Cell Value<1textNO
J20Cell Value>0textNO
Cells with Data Validation
CellAllowCriteria
B6:D6List=Crowd
B7:D7List=Zones



Costs - Working version.xlsx
GHIJKLM
1Base RatesSaturday RateSunday RatePH Rate
2HTP Per hour$ 35.87$ 53.80$ 71.74$ 89.68
3Event Staff Per Hour$ 35.87$ 53.80$ 71.74$ 89.68
4Pre Clean Per Hour$ 35.87$ 53.80$ 71.74$ 89.68
5Post Per Hour$ 35.87$ 53.80$ 71.74$ 89.68
6Post Sup Per Hour$ 35.87$ 53.80$ 71.74$ 89.68
7Event Sup Per Hour$ 35.87$ 53.80$ 71.74$ 89.68
8
Sheet2
Cells with Data Validation
CellAllowCriteria
K1List=Crowd
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Well, you could use a VLOOKUP, but your descriptions in col "H" on Sheet2 MUS be exactly the same as the desciptions in COL A of sheet1
IN J10
Excel Formula: 
=IF(OR(WEEKDAY(C10<>1),WEEKDAY(C10<>7)),VLOOKUP(A10,Sheet2!$H$2:$L$8,2,0),IF(C10="PH",VLOOKUP(A10,Sheet2!$H$2:$L$8,5,0),VLOOKUP(A10,Sheet2!$H$2:$L$8,4,0)))*I10
AND it looks like your missing a couple of categories in Sheet2 table
 
Upvote 0
Edit formula
Excel Formula: 
=IF(OR(WEEKDAY(C10<>1),WEEKDAY(C10<>7)),VLOOKUP(A10,Sheet2!$H$1:$L$8,2,0),IF(C10="PH",VLOOKUP(A10,Sheet2!$H$1:$L$8,5,0),VLOOKUP(A10,Sheet2!$H$1:$L$8,4,0)))*I10
 
Upvote 0
Sorry, forgot "Sunday"
Excel Formula: 
=IF(OR(WEEKDAY(C10<>1),WEEKDAY(C10<>7)),VLOOKUP(A10,Sheet2!$H$1:$L$8,2,0),IF(C10="PH",VLOOKUP(A10,Sheet2!$H$1:$L$8,5,0),IF(WEEKDAY(C10=7),VLOOKUP(A10,Sheet2!$H$1:$L$8,3,0),VLOOKUP(A10,Sheet2!$H$1:$L$8,4,0)))*I10)
 
Upvote 0
I note all your pay rates are the same for the staff....If you changed your table on Sheet 2 to
Book2
HI
10Rates
11Monday$35.87
12Tuesday$35.87
13Wednesday$35.87
14Thursday$35.87
15Friday$35.87
16Saturday$53.80
17Sunday$71.74
18Public Holiday$89.68
Sheet2

You would only need this formula
Excel Formula: 
=INDEX(Sheet2!$H$11:$I$18,MATCH(C10,Sheet2!$H$11:$H$18,0),2)*I10
OR even better
Excel Formula: 
=VLOOKUP(C10,Sheet2!H11:I18,2,0)
 
Upvote 0
This is getting very close! Thanks for working through it.

I've updated the table on sheet 2 to match the roles on sheet 1. I've also updated the Vlookup to match the new values of the table on sheet 2.

Now having updated and implemented your forumla, even when shifting to a sunday event, the standard base rate is still the one the pulls through. I can't source why it is not selecting the column index number successfully... Any ideas?

Also, i appreciate the advice on the change to the rates table, but having the broader table allows for changes to individual role rates in the future.

Costs - Working version.xlsx
ABCDEFGHIJK
1DateSunday, 18 April 2021
2Event
3Corporate Gates18:15
4Public Gates18:40
5Kick Off19:55
6Crowd Size10,001,15,000
7Zones Active6 Zones
8
9RoleNo. StaffDayDateStartEndShift HoursBreakHours EngagedCost
10Pre Clean 11Sunday18/04/202110:0019:009:000:308.50$35.87
11Pre Clean 20Sunday18/04/202110:0014:004:000:000.00$0.00
12Event Supervisor1Sunday18/04/202113:0023:0010:000:309.50$340.77
13Event Staff 16Sunday18/04/202117:4522:455:000:0030.00$1,076.10
14Event Staff 20Sunday18/04/202118:4522:454:000:000.00$0.00
15HTP10Sunday18/04/202118:4522:454:000:0040.00$1,434.80
16Post Supervisor1Sunday18/04/202122:304:306:000:305.50$197.29
17Post Staff40Sunday18/04/202123:303:304:000:00160.00$5,739.20
18Total Cost$8,824.02
19Charge Out$11,813.05
20P/L$2,989.03
21
Sheet1
Cell Formulas
RangeFormula
C10:C17C10=TEXT($B$1,"dddd")
D10:D17D10=$B$1
G10:G17G10=IF(F10<E10,F10+1,F10)-E10
H10:H17H10=IF(G10>TIME(5,0,0),TIME(0,30,0),TIME(0,0,0))
I10:I17I10=(B10*G10-(H10*B10))*24
J10J10=IF(OR(WEEKDAY(C10<>1),WEEKDAY(C10<>7)),VLOOKUP(A10,Sheet2!$H$1:$L$9,2,0),IF(C10="PH",VLOOKUP(A10,Sheet2!$H$1:$L$9,5,0),IF(WEEKDAY(C10=7),VLOOKUP(A10,Sheet2!$H$1:$L$9,3,0),VLOOKUP(A10,Sheet2!$H$1:$L$9,4,0)))*I10)
J11:J17J11=IF(OR(WEEKDAY(C11<>1),WEEKDAY(C11<>7)),VLOOKUP(A11,Sheet2!$H$1:$L$9,2,0),IF(C11="PH",VLOOKUP(A11,Sheet2!$H$1:$L$9,5,0),VLOOKUP(A11,Sheet2!$H$1:$L$9,4,0)))*I11
J18J18=SUM(J10:J17)
J19J19=SUMPRODUCT((B6=Sheet2!A24:A30)*(B7=Sheet2!B23:D23)*Sheet2!B24:D30)
J20J20=J19-J18
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C10:C17Cell Valuecontains "Sun"textNO
C10:C17Cell Valuecontains "Sat"textNO
J14Cell Value<1textNO
J10:J17Cell Value<1textNO
J20Cell Value<1textNO
J20Cell Value>0textNO
Cells with Data Validation
CellAllowCriteria
B6:D6List=Crowd
B7:D7List=Zones


Costs - Working version.xlsx
GHIJKLM
1Base RateSaturday RateSunday RatePH Rate
2Pre Clean 1$ 35.87$ 53.80$ 71.74$ 89.68
3Pre Clean 2$ 35.87$ 53.80$ 71.74$ 89.68
4Event Supervisor$ 35.87$ 53.80$ 71.74$ 89.68
5Event Staff 1$ 35.87$ 53.80$ 71.74$ 89.68
6Event Staff 2$ 35.87$ 53.80$ 71.74$ 89.68
7HTP$ 35.87$ 53.80$ 71.74$ 89.68
8Post Supervisor$ 35.87$ 53.80$ 71.74$ 89.68
9Post Staff$ 35.87$ 53.80$ 71.74$ 89.68
10
Sheet2
 
Upvote 0
And a larger problem seems to present itself when shifts will start on one day (say friday) then roll over to the next (saturday).
The v look up will run all 'hours engaged' against the day that the shift begins (event day).
Is there a way to have the vlook up alter maybe based on times pre midnight and post once a start day is given?

Thanks so much!
 
Upvote 0
Haven't got Excel at the moment, but try
Excel Formula: 
=IF(OR(WEEKDAY(C10<>1),WEEKDAY(C10<>7)),VLOOKUP(A10,Sheet2!$H$1:$L$9,2,0),IF(C10="PH",VLOOKUP(A10,Sheet2!$H$1:$L$9,5,0),IF(WEEKDAY(C10=7),VLOOKUP(A10,Sheet2!$H$1:$L$9,3,0),VLOOKUP(A10,Sheet2!$H$1:$L$9,4,0)))*I10)*B10
 
Upvote 0
Modified....UNTESTED
Excel Formula: 
=IF(AND(WEEKDAY(C10>1),WEEKDAY(C10<7)),VLOOKUP(A10,Sheet2!$H$1:$L$9,2,0),IF(C10="PH",VLOOKUP(A10,Sheet2!$H$1:$L$9,5,0),IF(WEEKDAY(C10=7),VLOOKUP(A10,Sheet2!$H$1:$L$9,3,0),VLOOKUP(A10,Sheet2!$H$1:$L$9,4,0)))*I10)*B10
 
Upvote 0
Realized we can't use WEEKDAY this way
Excel Formula: 
=IF(NOT(OR(C10="Saturday",C10="Sunday",C10="PH")),VLOOKUP(A10,Sheet2!$H$1:$L$9,2,0),IF(C10="PH",VLOOKUP(A10,Sheet2!$H$1:$L$9,5,0),IF(C10="Saturday",,VLOOKUP(A10,Sheet2!$H$1:$L$9,3,0),VLOOKUP(A10,Sheet2!$H$1:$L$9,4,0)))*I10)*B10
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Calc Finish Date from known Start Date and Duration across 2 shifts on different Workdays
Replies
0
Views
559
PGC14
P
P
  • Question Question
Count all overlapping days from range between another set of dates
Replies
1
Views
3K
Pepperoni
P
P
  • Question Question
Shift Pay Calculations
Replies
2
Views
666
paul_r26
P
S
  • Question Question
Calculating Total Time across Multiple Periods
Replies
3
Views
634
Shines1984
S
J
  • Question Question
trying to make a sheet that lists the hours of the day down column A
Replies
6
Views
656
jvoortman
J

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top