I downloaded your file, but I'm not sure what the formula in cell S9 is doing.
=IF(B9=0,"",IF(OR(WEEKDAY(A9)=1,WEEKDAY(A9)=7,$S$6="",B9<=$S$6,B9<=G9,B9<=J9,B9<=M9,B9<=P9),0,B9-$S$6))
As far as I can see, S9 is empty if B9=0, if B9 is not empty and if A9 is a weekday, S6 is not empty, B9 is greater than S6, G9, J9, M9, and P9, then S9 = B9-S6, if not then S9=0.
You mention in your file that S9 should be 7 minutes late. But the formula actually returns 0 because G9=4, which is greater than B9. B9 is formatted as time (13:37 or 1:37:00 PM). Remember that Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day. Therefore, 13:37 is actually stored as 0.567361111111111 (13/24 + 37/60/24). So as long as there is 1 or greater in either S6, G9, J9, M9, or P9, then the formula will return 0. This is one issue that I think you need to sort out.
The other issue is how B9-S6 is supposed to return 7 mins. In your example B9=13:37 and S6=8:30 AM. Subtracting the two returns 5:07. This is the same with row 13. How is B13-S6 supposed to return 35 minutes, when B13=14:05 and s6=8:30AM? Changing S6 to 13:30, will return the correct results, assuming you fix the first issue with the values in columns G, J, M, and P.
I hope this helps. Let me know if you have additional questions.
Bookmarks