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!
Thanks