Formula results in 'circular reference'

[nje]

I couldn't think of a suitable title but I'll try and explain.
I have attached the sample.
What I want seems to be very complicated and I don't know if it can be done or not.
I've already had lots of valuable help and just two things need to be ironed out if possible.

The attached timesheet is based on a working day of 7 hours 24 minutes. (I've hidden the 7:24 in row14)
The times are inserted in the cells IN, OUT, IN, OUT
The total hours are in row 13
and Row 15 works out the diference between the actual hours worked relative to the 7:24 for the purpose of flexitime as either credit or debit.
Everything works fine when the first IN & OUT are filled in, eg column 'D' of my sample sheet when only the morning is filled in. and in column 'K' when the complete day is filled in, But when only part of it eg, IN, OUT, IN are complete the calculation goes wrong, showing -9:00 hourstotal time and in row 15 16:24.hours

I tried this formula
in row 13, but I get a circular reference warning, and to be honest I've lost the plot and am stumped. =IF(OR(E8=0,E13=0),"",(E13-E14)),IF(E10=0),"",(E10-E9)+(E8-E7)
What I want to do is only calculate E8-E7 when E9 is filled in and then only do the full calculation of (E10-E9) +(E8-E7) when E10 is complete. If by any chance anyone can understand what I've just tried to explain you deserve a medal, because even I am confused now.
Thanks in anticipation
(edited) I've altered the formula a bit because I missed a bit out but it still doesn't work. Any ideas?

[6StringJazzer]

Here is a revision that I think meets your needs. Here is what it does:

Row 11 (Lunch) is calculated only after the OUT/IN for lunch break are both entered. This is calculated as soon as these are entered but regardless of whether the OUT time at the end of the day is entered.

Row 13 (Total Hours) is calculated after all IN/OUT times for the entire day have been entered.

Row 15 (Debit/Credit) is calculated after Row 13 is calculated.

[nje]

Excellent work, how you understood all that I'll never know. I hope you don't think I'm ungrateful for your solution because I'm not, It's a near perfect solution with just one exception, If they only work for a few hours e.g. 9:00 (in) to 13:00 (out) the lunch would remain empty which is what is needed but the total hours and the crdit/debit time wouldn't calculate at this point would they? Sorry to ask but have you any ideas?
Thanks.

[6StringJazzer]

Excellent work, how you understood all that I'll never know. I hope you don't think I'm ungrateful for your solution because I'm not, It's a near perfect solution with just one exception, If they only work for a few hours e.g. 9:00 (in) to 13:00 (out) the lunch would remain empty which is what is needed but the total hours and the crdit/debit time wouldn't calculate at this point would they? Sorry to ask but have you any ideas?
Thanks.

Is is OK to show the total hours for the day and debit/credit if the person is currently out to lunch and hasn't worked his whole day yet? If so I can make a tweak to take that into account.

Otherwise you need some sort of additional design feature to distinguish between someone who has left for the day vs. someone who is just out to lunch.

[nje]

6Stringjazzer, - If I understand you correctly, that's exactly what I want. So if someone comes in at 8:00 and only works until 12:00, the total time will show 4 hours and the debit will show 3:24 hours, No lunch time is required. If they were working all day they would just complete the additional in & out columns See my example Column C, D, E
Thanks again for the trouble you are going to , to help.

[6StringJazzer]

I think this is what you want, have a look. It calculates hours for the day (and also credit/debit) if all IN times have a matching OUT time.

[nje]

Thank You very much, exactly what was needed. just to help my slow progress with excel could you tell me what the C9<>=0
part of the formula is doing, I understood the <> to mean less than or greater than. Then I'll leave you in peace. Thanks again

[6StringJazzer]

Thank You very much, exactly what was needed. just to help my slow progress with excel could you tell me what the C9<>=0
part of the formula is doing, I understood the <> to mean less than or greater than. Then I'll leave you in peace. Thanks again

The formula, for those following along at home, is

=IF(OR(C8=0,AND(C9<>0,C10=0)),"",C10-C9+C8-C7)

You are correct that it means "less than or greater than." That is Excel's cute way of saying "not equal to."

I don't know how this convention arose; it is the same as in the Basic language. But other languages I've used have a distinct notation for this, such as != in Java and C, or /= in Ada.

An equivalent expression in Excel would be

NOT(C9=0)

but nobody really does that.

[nje]

Thanks, I'll try to digest that information with all the other things I'm learning about excel