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



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!




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



MacBook Pro, OS X Mountain Lion (10.8.3)
  • jaxjason Level 4 (3,535 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



         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



  • Jerrold Green1 Level 7 (29,960 points)



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



  • giltober Level 1 (0 points)

    the 45 min are not overtime!


    all the shift are between 7-10 hours



  • Jerrold Green1 Level 7 (29,960 points)

    giltober wrote:


    the 45 min are not overtime!


    all the shift are between 7-10 hours



    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?