Time Sheet Spreadsheet & Multiple IF Query

[ABARKIE]

Could someone offer some help on an excel command please?

I have a formula =IF(OR(B4I9),(SUM(D4-B4)-C4)-I10,"") for working out times on a spreadsheet.

Cell References are :

B4 = Start Time- Input
C4 = Lunch
D4= Finish Time - Input
E4 = Hours worked - Formula - IF(B4>0,(SUM(D4-B4)-C4),"")

I9 = Finish Time + 10 minutes
I10 = Working day (ie 7.5 hours)

Then what I want is that if people start before 8:20 (I8) and finish after 5:10 (I9) then it will work out the overtime but if I use the or command then it will work out the complete time if just one argument is correct i.e. if they start before 20 past 8 if will work out overtime for 1 minute past 5 (where I don’t want it to work it out unless its after 10 past)

But if I use the AND command it wont work it out until both arguments are correct i.e. someone comes in at 25 past but does 2 hours after 5 if wont work out the time because they didn’t start before 20 past 8.

Is there a formula that will take both commands into consideration when working it out?

Thanks in advance

Alan

[arthurbr]

Welcome to the board
This link will get you started :http://cpearson.com/excel/overtime.htm
HTH

[imnew]

I am currently compiling a complete business tool that counts lots of different things like figures, rota, cash loss, wage and PAYE etc etc

However i am finding increasingly difficulty to calculate the time as i need it. I currently use eg "=mod(a2-a1,1)*24" to give me the actual hours worked. What i want to do with this figure is now tell it that should it be over 6 hrs to deduct 0.50 hrs off....but if it is over 12 hrs to deduct another 0.25 off. This is where i am failing.

Can someone help me without me having to do timecards like the whole of youtube seem to think people work ie clock in and out lol

Thanks for any help people...

[protonLeah]

imnew,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.