Time Sheet Spreadsheet & Multiple IF Query

Time Sheet Spreadsheet & Multiple IF Query

Didn't find your answer?

Could you 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 Barcroft

Replies (1)

Please login or register to join the discussion.

avatar
By User deleted
14th Feb 2007 21:48

Expand the lines
There are three options
1 expand the formula in one cell
2 increase the number of columns
3 include a static reference to one cell

All are variations of the theme; set a cell with the latest start time and test against this and set another cell as the earliest end time and test against this.

As a rough rule of thumb, the greater the cell usage and less conglomerated cell formulae the better.

I assume you can work it from there.

Regards,

J

Thanks (0)