Timesheet, hours worked with multiple varriables...

ive been working on this timesheet for a day or so getting no were fast hoping someone could give me some ideas.
this timesheet require multiple variable Regular time, Time 1/2 and Double time.
all on different pay levels Shop Hours, Travel Hours and Onsite service Hours.
For regular time only Shop hours and Travel will use this being Regular time up until 40 hours then time 1/2 until 56 then double after.
for Onsite hours they are Time 1/2 until 40 hours then double time.
i think my biggest issue is all the hours work together say if i get 20 hours shop time 20 hours travel time then when i start my onsite time im already at double time and vise versa if i hit 56 hours onsite time when i get back to the shop im on double.Book1.xlsx
Also im new to excel just started playing couple days ago so please explain any ideas you may have i do not know how all the formulas work yet. Attached is part of my project....
Thanks

Hi Biozombie,

It is difficult to provide answers because there are so many questions. The sample you provided is difficult for me to follow.

1. There is no label on row 8, but 20 in cell C8. What is this? (Perhaps ONSITE?)
2. I assume this is a time sheet for an individual! Clearly he/she could not work 20 hours in any one day.
3. Do penalty rates change if work is done at the weekend?
4. Can I restate your pay structure another way? Is this the way it works?
- An employee is paid ordinary time for hours worked in the shop, or travelling UP TO 40 hours. The next 16 hours worked are at X1.5 then DOUBLE TIME over 56 hours. HOWEVER, any on-site hours are paid at X1.5 and X2.0 for over 40 hours.
5. I assume that if an employee works 8 hours on-site early in the week, even though they are paid at X1.5, the hours are counted as ordinary hours for the purpose of calculating when overtime kicks in at 40 hours. Is this how it would work?

It would be useful if you could answer these questions, and perhaps post a new sample sheet with real values in the first few rows, and the expected values in the shaded rows where you want us to provide the formulas.


Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

1.)A5-A8 will be onsite jobs sometimes multiple companies are visited in a day.
2.) yes it is for a individual i was just using 20 for testing and sometimes we do work over 20 in a day say i fly overseas to china allot of times they are wanting us to work ASAP.
3.) the only thing that will change on the weekend is Sunday travel is always double time.
4.) ive tried to change this sheet before the people who receive this sheet do not like change i know there are better ways it could be handled and less complicated ways there words not mine "its been done this way for 30 years" lol im just trying to make it less complicated withought changing the look.
5.)yes all the hours basically work together this is just hours to be paid if i work 40 hours onsite at X1.5 then travel home Thursday i will be making x1.5 on travel and shop.
sorry for the mess i should have cleaned it up some most of the junk was just trying to get it to do what i wanted, for testing purposes Rows 13-16 are not working as id like them to the code is mostly junk i myself have not been able to make this count hours correctly. i uploaded a cleaned up version Book2.xlsx
thanks for your help if you could just throw me some sample code for the first few days i should be able to finish the rest im trying to teach myself excel. again thanks.

Book3.xlsx sorry uploaded the wrong file this is the sample you want.

Hi Biozombie,

I've had a bust day today, and unable to deal with your problem. I will look tomorrow.

Regards, David

Hello again Biozombie,

This is a quite complex issue!

As requested, I have provided you with some code, BUT IT IS INCOMPLETE! The main area where more work is needed is where you hit the 40 hour mark - there may be problems in splitting the hours between overtime and double time.
The code I have entered provides the answers you expected, but I am mindful of the transitions, and if I had more time I would attend to it.

You did say you are keen to learn, so hopefully you can follow what I am trying to do.

biozombie.xlsx

First of all, I added a new row showing the accumulated hours - this makes the formulas a lot shorter!

Basically, each of the three formulas in cells C14:C16 contain a formula which can be copied across. The first half of the formula (before the PLUS sign) is calculating the Shop Hours and Travel time, the second half tries to get the rest. (this second half may need more work).

Please have a look at what I've done and see how you go.

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

yea thats basically the same formula i came up with i tried a few more things just couldn't come up with something working this formula has the same issue mine does. the formula you sent me for onsite time if i get 10 hours mon-thu when it hits 40 hours it does not count them hours for the day same if i was to reach 56 hours and input a travel or shop time, say i get 12 12 12 12 when it reaches 48 for that day i should have 4 hours overtime 8 hours double but i cant figure out a way to make it retain the 4 hours overtime it puts it in double time. thanks for taking a look think ima take a week break with it and revisit some other time.

Since you're not under huge time pressure, I'll have a look over the next few days and see if I can improve.

I think it can be done. It just takes a lot of thinking to get the logic right.

Are you comfortable with "helper cells (rows)" where calculations are performed elsewhere, and the results reported in your table? This approach often makes it a lot easier than trying to calculate the whole answer in one cell. The resultant formula being almost impossible to follow when you revisit it later.

If this is OK, pls let me know and I'll try a lightly different approach.

Regards,

David

No I don't have no problem with that I can always hide them cells. That was my first approach was using helper cells when I started but then I knew nothing about excel I kinda crammed my head in a few days. Thanks again for the help I'm going to revisit it this weekend. I'm really in no rush so don't hurt your brain mine is healing right now rofl...

Hi bizombie,

This problem has nearly done my head in, and I am sure I still haven't got it perfect.

Here is what I've come up with.

biozombie v3.xlsx

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

nice work i like it. didnt have much time to really look at it been traveling all day im shure ill do a little tweaking ill make shure to post the final results... thanks a million ill let ya know how it all works out by this next weekend....