Calculating Shift Premiums and Weekends

[Shawnkainth]

Hi,

I have attached a sample spreadsheet which I am building to calculate an employees shift premium payment each day they work.

For example any hours worked between the following hours attract a payment premium as detailed...

Majority of the hours are between 6am and 6pm (day)

Majority of the hours are between 6pm and 6am (night)

I have the weekday hours all worked out but am having trouble getting the weekends to work.

Saturday shifts start at OT for up to 7.5 hours and are DT after that.
Sunday shifts are all DT.

There is something wrong with my formula but I am having trouble finding out what it is. Any help would be much appreciated!!

[Limor_OP]

Hi
what are the correct values you expect tp see for suterday lines?

[Shawnkainth]

Saturday shifts should look as the following:

12 hours

OT: 7.5
DT: 4.5

7.5 hours

OT: 7.5

10 hours

OT: 7.5
DT: 2.5

[Limor_OP]

Can you please upload an excel with a maually entered results with requested outcomes?
All your formulas from K-Q dont produce the right result?

[Shawnkainth]

Please see attached

[Limor_OP]

Thanks.
Now another thing:

Can you please put in words all the conditions you wanted to incorporate in the formula below?
divided to sections (1,2,3,....)
For example:
1. If J = "night" then _____
2. If J = "Night" and K> 7.5 then _____

I'm afraid your formula is not constructed correctly at all. If you breakdown all your conditions in a streamline way perhaps we can re-construct the formula properly.

M6 =
=IF(OR(J6="NIGHT",0,IF(WEEKDAY(A6)=7,IF(K6>=7.5,(7.5)),IF(((K6-7.5)>3.5),3.5,(K6-L6)))),IF(J6="NIGHT",0,IF(WEEKDAY(A6)=1,IF(K6>=1,(0),(K6)),IF(((K6-7.5) > 3.5),3.5,(K6-L6)))),IF(OR(WEEKDAY(A6) < > 1,WEEKDAY(A6)<>7),IF(K6 >=7.5,(0),IF(((K6-7.5) >3.5),3.5,(K6-L6)))))

[Shawnkainth]

I need to be able to toggle between day and night shifts to have hours allocate in the correct columns. I have set up the the night and day toggle for that.

Below is what I want the breakdowns to look like to weekday day shifts

Hours <= 7.5 hours

Straight time hours

7.5 <= Hours <= 11
Overtime hours

Hours > 11
Double time hours

For example:

7.5 Hours
ST: 7.5

10 Hours
ST: 7.5
OT: 2.5

12 Hours
ST: 7.5
OT: 3.5
DT: 1

Below is what I want the breakdowns to look like to Saturday day shifts

0 <= Hours <= 11
Overtime hours

Hours > 11
Double Time hours

For example:

7.5 Hours
OT: 7.5

10 Hours
OT: 7.5
DT: 2.5

12 Hours
OT: 7.5
DT: 4.5


Below is what I want the breakdowns to look like to Saturday night shifts

0 <= Hours <= 11
Overtime hours

Hours > 11
Double Time hours

For example:

7.5 Hours
1.875: 7.5

10 Hours
1.875: 7.5
2.5: 2.5

12 Hours
1.875: 7.5
2.5: 4.5


Below is what I want the breakdowns to look like to Sunday day shifts

All hours are double time

Below is what I want the breakdowns to look like to Sunday night shifts

All hours are 2.5 times

[Limor_OP]

Thanks. What about 4 hours night Saturday?
Where would it go? to N-1.25?

[Limor_OP]

Anyway I applied the formulas in L11-Q11 as follows.
Please test them to see if they produce the requested results:


L11: =IF(AND(J11="day",WEEKDAY(A11)<>7,K11>=7.5),7.5,0)

M11 =IF(AND(J11="day",WEEKDAY(A11)<>7,K11<=11),K11-7.5,IF(AND(J11="day",WEEKDAY(A11)<>7,K11>11),3.5,IF(AND(WEEKDAY(A11)=7,J11="day",K11<=7.5),K11,IF(AND(WEEKDAY(A11)=7,J11="day",K11>7.5),7.5,0))))

N11 =IF(AND(J11="day",WEEKDAY(A11)<>7,K11>11),K11-11,IF(AND(WEEKDAY(A11)=7,J11="day",K11>7.5),K11-7.5,IF(AND(WEEKDAY(A11)=1,J11="day"),K11,0)))

O11 =IF(J11="DAY",0,IF(OR(WEEKDAY(A11)=7,WEEKDAY(A11)=1),0,IF(K11>7.5,(7.5),(K11))))

P11 =IF(AND(J11="night",WEEKDAY(A11)=7,K11<=7.5),K11,IF(AND(J11="night",WEEKDAY(A11)=7,K11>7.5),7.5,0))

Q11 =IF(AND(J11="night",WEEKDAY(A11)=7,K11>7.5),K11-7.5,IF(AND(J11="night",WEEKDAY(A11)=1),K11,0))

[Shawnkainth]

Works like a charm! Few small changes that I had to make but overall excellent!

Now how do I calculate the difference in start and end time?

[Limor_OP]

Hi
Glad to hear that.

What do mean by "difference" between start and end time?
isnt column K calculating that?

[Limor_OP]

Also I noticed there was a problem still with Sundays so I adjusted the formulas as such:

L11 =IF(AND(J11="day",WEEKDAY(A11)<>7,WEEKDAY(A11)<>1,K11>=7.5),7.5,0)

M11 =IF(AND(J11="day",WEEKDAY(A11)<>7,WEEKDAY(A11)<>1,K11<=11),K11-7.5,IF(AND(J11="day",WEEKDAY(A11)<>7,WEEKDAY(A11)<>1,K11>11),3.5,IF(AND(WEEKDAY(A11)=7,J11="day",K11<=7.5),K11,IF(AND(WEEKDAY(A11)=7,J11="day",K11>7.5),7.5,0))))


The rest is the same....

[Shawnkainth]

Hi Belinda,

The new sheet does not work for when hours worked is less than 7.5.

[Shawnkainth]

Please see sheet attached.

Saturday times do not work out as they should. For examples see below

Hours worked: 9
ST Hours: 0
OT Hours: 7.5
DT Hours: 1.5

Hours worked: 6
ST Hours: 0
OT Hours: 6
DT Hours: 0

Hours worked: 7.5
ST Hours: 0
OT Hours: 7.5
DT Hours: 0

Can someone help me? Weekdays and Sundays seem to work perfectly fine.

[Limor_OP]

Please see sheet attached.

Saturday times do not work out

Are you referring to Sut. nights or days shift?
If it's a day it seems to work - just take the right formula from lines 11/12
why are you using different formula in lines 19-24? I dont think this is what I gave you...