Complex overtime worksheet

[callmerob]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I can calculate my overtime into 2 diferrent rates including weekends, but I need to enter manually any 2UP (Row/Cell AH) or Wait time hours (Row/Cell AM)which both need to be deducted from the daily hours before calculating the overtime K19 is the Calculated Total hours paid for the day<o></o>
This Spreadsheet calculates overtime each day after 10 hours regular time or after 38 hours of regular time per week. 3hrs max pay at x 1.5 then balance x 2 <o></o>
Overtime formula is<o></o>
=MAX(IF($AO$8,MAX(0,SUM(N18:N18)+K19-$N$3),0),IF($AO$5,IF(K19>$K$3,K19-$K$3,0),0))<o></o>
<o> </o>
Can anyone help??? Please!!!
Thank you

 

[Gerald Higgins]

Hi Rob, welcome to the board. Your request is pretty confusing. I think I understand in general what you want to do, but I can't provide a specific answer because I can't make sense of how your data is laid out, or what exactly you need help with.

Can you try and explain REALLY CLEARLY what data you have, how it is laid out, and what you want to do with it.

 

[callmerob]

Hello
I have a very large excel sheet that I enter Time& Date Start & Finish, I enter the unpaid break time and also details of the work done and a drop list with Job codes. the Sheet is then split into 6 columns which I have calculating the hours worked into normal time and overtime, all of this works so far. I have really used an excel template as a base to work all this from - so at the top of the sheet I have it set to allow 38hrs normal work per week and up to 10hrs/day (Max 38/week) all the rest is overtime. The Normal time includes a casual loading but the overtime does not. this is all ok so far.
The problem I am trying to overcome is that I have another 6 or 7 catergories which is calculated back to just to columns of catergories which I need to overide manually Wait Time and 2Up Driving hours
I would like each of these 2 columns to be deducted from the daily hours worked before calculating normal and regular hours because these 2 catergories are not part of the calculation for overtime purposes.
References for 2UPHours is (Row/Cell AH) or Wait time hours is (Row/Cell AM)
Reference K19 is the Total hours to be paid for the day
So I want to deduct AH19 and Also AM19 from the below formula

=MAX(IF($AO$8,MAX(0,SUM(N18:N18)+K19-$N$3),0),IF($AO$5,IF(K19>$K$3,K19-$K$3,0),0))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>


If I could send you a screen shot (I am ask for a URL?)or the file itself I would, but I am very new to this and have not done it before.
Thanks

 

[Gerald Higgins]

Sorry Rob, this isn't any clearer, at least to me. I dont' know what 2UPHours are, and I don't know what "casual loading" means. I think I know what "Wait time" is, but maybe it's something different for you.

But maybe I don't need to know any of this stuff, because at the end of the day it comes down to a bunch of numbers on a worksheet, and I have no idea what they are, or what you want to do with them.

For example, you say you want to deduct AH19 and AM19 from the formula.

Well, at a very basic level, you can just add

-AH19-AM19

to the end of that formula.
But I'm guessing you mean something a bit different, but I don't know what.

Sorry if this is sounding harsh, that isn't my intention.

There are several ways of posting screenshots of your data.
Also, you could try just mocking up a simple version of your data within the text of a normal post.
Or, you could try saying something like "I have basic hours in cell Z99, 2UPHours in cell AX99 . . . . . and this is what I want to do with them."

Good luck.

 

[callmerob]

I worked out the problem- I was trying to deduct the 2 other Hours catergories from the wrong column - No wonder it wouldn't work !
Thanks for the help everyone