using Functions to Code Overtime Calculations having to do with time in excess of 40h

ok , ..... ive put some hours into this trying to get it to work for my mother and her job and it has been through a few revisions and each time i have been told what needs to be done for it i have come off sucsessfull,..... i finally hit a brick wall and i am branching out for help.


as you take a breif look at it the data is now split on 2 pages. it was the only way i could think i was going to be able to achieve what i want it to do.
the one on the left is the one you enter data into the one on the right will be the one to be printed.

so what needs to happen in the contract row and wharehouse row is that at the time and on the day they hit 40hrs all additional time go's in to the overtime tally , which the overtime is calculated correctly, but on my page 2 in row "Q" for example it should take the 11 hrs and keep 6 of them to make the total 40 and the rest should be in over time ,.... my dellima is evry time i try and wrap my brain arround it ,... it frys becaus there are 2 diffrent variables to which accumulate hours in a day. So i will make a copy with brown fonts where i changed the cells from there current formating to display what should go there.

its just confusing caus somtimes there can be somone who works contract time and wheare houst time and once it gets to a total of 40 betwine the 2 all te rest need to go into an overtime slot. its just been a headach and i hope somone can point me in the right direction.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

First let's just chuck up a few pointers to make your existing formaule a little easier to adapt:

Page1

I4: =SUM(B5:H5)
ie given range is contiguous use the range rather than having to add cells individually.
(can be applied to I5, I6, I12 -- as for I13 you could change to =SUM(I5:I7))

I8: =MAX(0,I5-I9)
I9: =MAX(0,(I5+I7)-40)

In a similar vein:

B10: =SUM($B13:B13)
copy across for C10:H10
by using a mix of absolute and relative references the formula will adapt itself automatically as it's expanded across a range (no need to manually adjust each formula individually)

B12: =MAX(0,B10-40-SUM($A12:A12))
copy across for C12:H12
as per B10 this uses mixed references to ensure you get a nice rolling total without having to adjust each individual formula in the range to account for prior cells.

obviously what holds true for your first table holds true for your 2nd entry table (11th Jan 2009) in terms of the formulae -- so you can copy them to the same cells on the 2nd table.


As for your 2nd page ...

First off, as per page 1, make use of absolute/relatives for your running totals such that:

N10: =SUM($N13:N13)
copy across to T10

Now you're real problem... well obviously you will need to come up with some logic that dictates which of the hours are used up first in a non-overtime sense -- Contract or Where House ? ie as you say you have 11 hours to assign from Weds (9/2 split) however only 6 can be deemed non overtime so do you use the Contract hours first and then where permitting apply also to Where House or is it v-v (or worse will you try to apportion on relative % basis) ? If we assume the former - that is to say Contract Hours should be used up first:

N5: =IF(N12,MIN(N(B5),MAX(0,40-N(M10))),N(B5))
copy across to T5

N7: =IF(N12,MAX(0,MIN(40-(N(M10)+N5),N(B7))),N(B7))
copy across to T7

There are a variety of ways to do this -- the above represents just one such way so you may well get other solutions you deem preferable... if nothing else I hope the above helps you understand use of absolutes & relatives etc...

I have to confess I'm not sure how/where your circular references are emanating from as I just went through the instructions from scratch and it worked of for me

Regards $ -- check out Absolute / Relative Referencing in XL Help and via Google -- you should find plenty of material on the topic.

Cheers