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
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