Timesheet with incorporated pay calculator


Hi All,

I had a search but cant find a thread that relates quite to what im trying to do.

I am trying to create a timesheet which calculates pay by shift.  There are different pay rates depending on day and time.  I have these pay rates in cells off the main viewing area.  I thought the simplest way would be to divide the shifts down into 3 to incorporate unpaid breaks, so it actually looks like 3 different "mini shift" over 3 rows.  Im struggling to work out the required formulas to calculate each sections pay as the start time may be at a different rate to the finish time.  So for example, start time may be 8pm and finish at 1am but nightshift rate kicks in at 10pm. 

I know there are a few ways I can format the time as I am potentially having shifts running from one calendar day to the next.  Ive currently got it setup in the simplest form i can think of whilst trying to decipher the formulas - being a 39 hour clock (39 hour as a shift may last up to 15 hours and could start at 11.45pm - therefore the latest time on the clock would be 3pm at 39 running from the day before)  Am i best using a different format for this now and building the formula around that?

The last issue would be for the rates selection.  There are also different rates based on different clients.  Ive incorporated a drop down box so that I can choose the client, but how can i vary the rates in the formula to take into account what value was entered into the drop down box? For example Sunday rate on client 1 is 16ph and on client 2 is 13ph.

Hopefully that makes sense and you understand what im trying to achieve.  I managed to create something slightly less complex before with only 2 pay rates but i cant remember how!





You are trying to make a  massive spreadsheet here it looks like and the best way to go about this is to take it in pieces.

The first thing to do is to get the hours worked and wages calculation working.

First, follow our tutorial Calculate the Total Time Worked Minus Lunch Breaks in Excel to get the total hours worked with breaks.

In the above tutorial you will just have to expand the pattern used to subtract the single lunch break to subtract multiple breaks; that will make the formula larger and more complex but it shouldn't be too difficult.

Once you do that, you need to find out how many hours for that shift occur before 10pm and how many occur after 10pm. That's simple, just use 10pm as the ending time for one calculation and the starting time for another calculation and if there is a positive number result then that tells you how many hours happened.

I recommend storing the 10pm time in a separate section of the spreadsheet and just referencing the cell that contains that time instead of hard-coding it into your formulas.

This should get you started.


Answer the Question
