Nested IF + TIME functions for timesheet/rota

[JasonP91]

Hi there,
I haven't used excel properly in a few years, I know the gist of what I'm trying to do, but I can't quite get it to work. I'm creating a work rota in excel and I've come across a bit of a problem.
I currently have B4-start time, C4-end time, D4-total worked. D4 started easy just being "=C4-B4", but I need it to automatically deduct breaks. If C4-B4 is 8 or more, I need to deduct an hour, or if 6-7 i need it to do deduct 30 mins, less than 6, then deduct nothing.

For this current example, B4=09:00, C4=17:00
I can get the first part working, in D4 I have =IF((C4-B4)<=8,(C4-B4)-TIME(1,0,0))
That formula is giving 7 as the answer which is correct, but I cant add a second function in case the times are different (which they are each week)

The cells are currently in the format hh:mm.

Hopefully that all makes sense, if anyone can help or needs anymore information, please just say.

Thanks in advance,
Jason

 

[AliGW]

Re: Help using nested IF + TIME functions for timesheet/rota

Actually, you might need it to be this:

=IF((C4-B4) < TIME(5,0,0),C4-B4,IF(and((C4-B4), < TIME(5,0,0),(C4-B4), < TIME(7,0,0)),(C4-B4)-TIME(0,30,0),IF(and(C4-B4, > TIME(7,0,0),C4-B4, <=TIME(8,0,0)),(C4-B4)-TIME(1,0,0),C4-B4)))

 

[JasonP91]

Re: Help using nested IF + TIME functions for timesheet/rota

Thank you so much for all your help. The 1st one you posted worked fine, that second one doesnt work though... I'm now having a play with it to learn properly how it works and is formatted. I really appreciate everything you've done

 

[AliGW]

Re: Help using nested IF + TIME functions for timesheet/rota

My mistake - sorry - should be this:

=IF((C4-B4) < TIME(5,0,0),C4-B4,IF(AND((C4-B4) < TIME(5,0,0),(C4-B4) < TIME(7,0,0)),(C4-B4)-TIME(0,30,0),IF(AND(C4-B4 > TIME(7,0,0),C4-B4 <=TIME(8,0,0)),(C4-B4)-TIME(1,0,0),C4-B4)))

 

[JasonP91]

Re: Help using nested IF + TIME functions for timesheet/rota

The 1st one still seems to work best, that last one (although it no longer comes up with an error message), it doesnt do anything to any time under 8 hours like it should.

 

[AliGW]

Re: Help using nested IF + TIME functions for timesheet/rota

Try this:

=IF((C4-B4) < TIME(5,0,0),C4-B4,IF(AND((C4-B4) > TIME(5,0,0),(C4-B4) < TIME(7,0,0)),(C4-B4)-TIME(0,30,0),IF(AND(C4-B4 > TIME(7,0,0),C4-B4 <=TIME(8,0,0)),(C4-B4)-TIME(1,0,0),C4-B4)))

 

[JasonP91]

Re: Help using nested IF + TIME functions for timesheet/rota

Okay, so I thought I understood it, but I can't get another bit to work. I've followed your advice and tried to add in another clause to say if its 10 hours or over, remove 1h30mins... Excel accepts the formula, but it's not following the rule. I assume im missing something or have something in the wrong order?

Here's the formula:
=IF((C4-B4)<time(6,0,0),c4-b4, if((c4-b4)<time(8,0,0),(c4-b4)-time(0,30,0),="" if(or(c4-b4,time(7,0,0),c4-b4,time(8,0,0)),(c4-b4)-time(1,0,0),="" if((c4-b4)="">=TIME(10,0,0),(C4-B4)-TIME(1,30,0),C4-B4))))

EDIT: Sorry, but how do I post the full formula? It keeps cutting half of it?</time(6,0,0),c4-b4,>

 

[AliGW]

Re: Help using nested IF + TIME functions for timesheet/rota

OK - this is my final crack of the whip, as I have to go and get lunch!

=IF((C4-B4) < TIME(5,0,0),C4-B4,IF(AND((C4-B4) >=TIME(5,0,0),(C4-B4) < TIME(7,0,0)),(C4-B4)-TIME(0,30,0),IF(AND(C4-B4 >=TIME(7,0,0),C4-B4 <=TIME(8,0,0)),(C4-B4)-TIME(1,0,0),IF(C4-B4 >=TIME(10,0,0),(C4-B4)-TIME(1,30,0),C4-B4))))

 

[JasonP91]

Re: Help using nested IF + TIME functions for timesheet/rota

Spot on! Thank you so much for all you help, it's really appreciated!

 

[AliGW]

Re: Help using nested IF + TIME functions for timesheet/rota

I'm jolly glad we got there! Some of your parenthesis is not needed, but let's leave well alone now we've got it working!

 

[JasonP91]

Re: Help using nested IF + TIME functions for timesheet/rota

Right, so everything you've helped me with so far has been great... i'm probably making things more complicated than they need to be, but I'm trying to make the entry side of this rota as simple as can be. So far I'm using the formula you posted in post #17.

I've been trying to find a way to be able to enter the times without needing to type a colon which slows eveything down considerably. I've found a way to format the cell as "00\:00"... so now i can type in 0900, or 1700 for example and they appear as '09:00, or 17:00... but that now changes the addition/subtraction side of things...

I've found I can use the fomula "=TEXT(C4,"00\:00")-TEXT(B4,"00\:00")+(B4>C4)" to get the base addition/subtraction to work, but I can't fathom how to implement that into the formula you worked out for me. Any suggestions on how to use it would be greatly appreciated, or if you know of an easier way to not have to type a colon every time would help (but as far as I'm aware excel needs the colon to know it's time)...

Thank you