4 Replies Latest reply: May 9, 2013 7:45 AM by Jerrold Green1
Level 1 (0 points)

Hi

I started a new job job and I want to do a spreadsheet that will calculate my salary but there are a few "rules"

my hourly salary is 23.12.

the hours between 10pm to 6am are considered the "night shift", if i work at least 2 hours of the night shift my entire shift is considered night shift and i get 200% on the entire shift

example: if i start at 4am and finish at 11am or start at 4pm and finish at 12am because i did at least 2 hours on the night shift the entire shift is at 200%.

during the day the 9th and 10th hour  of my shift are 125% and the 11th and 12th are 150%.

how do i do this?

thanks a lot!

p.s.

on every shift i do i also get paid for 30 min before the shift and 15 min after.

i want this calculating done on a different cell

Thanks...

MacBook Pro, OS X Mountain Lion (10.8.3)
• ###### 1. Re: calculating salary
Level 4 (3,325 points)

I am assuming that your shift is never broken up? i.e. 4 hours in the morning and 6 at evening non continuous? (btw, this would not be a salary, this is hourly. Salary you get the same pay no matter hours)

Does the 1/2 hour prior and 1/4 after get the multpliers? I would assume not, not work done. Does that equate into the 9,10,11 and 12 hour calculations?

my possible logic (in seudocode)... if right we can convert to very simple frmulas

IF (End time>=midnight) or (starttime<=4am) then

basepay=2*hours*rate

else

Base Pay= 8 * rate '(assume minimum 8 hour shift)

if Hours >8 and Hours <=9 then basepay=basepay + (1.25*rate*(hours-8))

if hours >9 then basepay=basepay + (rate*1.25*2) + (hours-9)*rate*1.5

end if

basepay=basepay+(.75*rate) 'Add prior and post hours

Jason

• ###### 2. Re: calculating salary
Level 7 (28,995 points)

Gil,

is the 45 minutes (30 before and 15 after) calculated at straight time, or is overtime rate applied?

Jerry

• ###### 3. Re: calculating salary
Level 1 (0 points)

the 45 min are not overtime!

all the shift are between 7-10 hours

Gil

• ###### 4. Re: calculating salary
Level 7 (28,995 points)

giltober wrote:

the 45 min are not overtime!

all the shift are between 7-10 hours

Gil

Good information. Can we assume that you will be entering both Date and Time for Start and End of shift, or will you enter time only?

Jerry