calculating salary

Question

Level 1

0 points

calculating salary

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)

Posted on May 8, 2013 1:01 PM

Reply

Answer 1

jaxjason

Level 5

5,047 points

May 8, 2013 2:07 PM in response to giltober

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

Reply

Answer 2

May 8, 2013 2:09 PM in response to giltober

Gil,

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

Jerry

Reply

Answer 3

giltober Author

Level 1

0 points

May 8, 2013 9:49 PM in response to Jerrold Green1

the 45 min are not overtime!

all the shift are between 7-10 hours

Gil

Reply

Answer 4

May 9, 2013 7:45 AM in response to giltober

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

Reply