Lookup formula best for this?

[RaRaAv1s]

As i'm pretty much newbie at excel finding it quite troublesome.

I'm making some sort of "Time Sheet", employee's fill out time the start job, and time they finish.

So for example

Start Time A1 = 8:00
Finish Time A2 = 10:00
Total Time A3 = 2:00 (SUM A2-A1)

Thats the simple bit.
However if A2 had a time of 10:45, it would actually need a formula to take 0:15 off, because we have tea break, from 10:00 - 10:15.
Furthermore it had a time of 14:00 it would need to take a further 0:45 off, for lunch break, from 13:00 - 13:45.


So if would someone would be kind enough, to help me get near to it. Cells don't matter I can change them around as needed, just the formula I'm stuck on.


Many Thanks
Stuart

[JohnTopley]

Try

=IF(A2>TIME(14,0,0),A2-A1-(1/24),IF(A2>TIME(10,15,0),A2-A1-(1/96),A2-A1))

or

=IF(A2>14/24,A2-A1-(1/24),IF(A2>10.25/24,A2-A1-(1/96),A2-A1))

In Excel 24 hours=1 so 1 hour=1/24 and 15 mins = 1/96

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

[RaRaAv1s]

Awesome thank you very much

[Richard Buttrey]

My pleasure and thanks for the rep.

[RaRaAv1s]

Ok tiny little problem.

If someone starts at 12:00, rather than 8:00, it's still taking the 0:15 off, that they would have at 10:00. But it doesn't need to because they started it after the tea break of 10:00.


I've been fiddling around trying different things, and it's got me stumped.


Thanks again.

[JohnTopley]

Try

=A2-A1-IF(A1<(10/24),0.25/24,0)-IF(A2>(13.75/24),0.75/24,0)

[RaRaAv1s]

Yep that works.

Thanks once again for your help

[JohnTopley]

Edited the last formula: see post #7