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

Try this:

=IF((C4-B4)<7,(C4-B4)-TIME(0,30,0),IF((C4-B4)<=8,(C4-B4)-TIME(0,30,0),C4-B4)))

 

[JasonP91]

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

Thanks for the speedy reply. You've got me closer, but not quite there (it no longer comes up with an error at least). Not sure how to explain it, but It's not detecting the 2nd part of the formula. I've adjusted yours to remove an hour if 8 or more hours, but it's still just removing 30 minutes because the 1st half of the formula is correct. Any suggestions?

Thanks again

 

[AliGW]

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

Try this:

=IF((C4-B4)<7,(C4-B4)-TIME(0,30,0),IF(OR(C4-B4,8,C4-B4,7),(C4-B4)-TIME(1,0,0),C4-B4))

 

[JasonP91]

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

Thanks again for the speedy reply. It's still only removing 30 minutes no matter the time.

 

[AliGW]

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

Then we may need to have a look at the workbook, as clearly something is awry. Unfortunately there is no attachment facility here, but I will take a look if you pop the workbook on DropBox and provide a link.

 

[JasonP91]

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

Thank you very much. I really appreciate the help. I 'inherited' the spreadsheet so there could be some bad code elsewhere I'm not aware of. Here's the link. https://www.dropbox.com/s/ttmsscm8eh2s0c9/Jason Rota Fuction Test v2.xlsx?dl=0

Thank you

 

[AliGW]

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

Here you go:

=IF((C4-B4) < TIME(7,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),C4-B4))<time(7,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),c4-b4))< html=""></time(7,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),c4-b4))<>

 

[JasonP91]

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

Thank you very much. That works so much better. Theres only one thing now, and i'm sorry to keep asking, but if it's 5 hours or below, i need there to be no change. Every other part of it works perfectly now though, thank you.

 

[AliGW]

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

It's just a case of adding another clause to the statement:

=IF((C4-B4) < TIME(5,0,0),C4-B4,IF((C4-B4) < TIME(7,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),C4-B4)))

I suggest you now try adding to and adapting the formula yourself so that you learn how it works.