Formula to identify by workhours multiple days

[dannwid]

Hello everyone,
I'm stuck on some issue about time formula.

Here is the case,
I have 7 main column, with 2 sub column each. Main column contain day, sub-columns contain In and Out. In means arrival time, and Out means leave time. Check the picture below.

Like i wrote on the image, how do i identify how many day(s) the employee get full time, not-full time, and not present (formula on blue fill area) ??

Above, on yellow fill (row 6), is the example of conditions, and the blue fill is the result (i identify 'em manually).

Starting hour is 8:30
End hours is 17:30
The conditions is shown on row 14,15,16

Heres the file of this case: DOWNLOAD

I hope i explained my question properly

 

[Col Delane]

You could try to write a single formula to enter in R6, S6 & T6, but that will be cumbersome and difficult to debug - so it's better practice to build some helper cells/columns (x 7 either between Q & R and then use Grouping/Outlines to collapse them under the Full column (now Z), or add them to the right of T, in say V:AB) to calculate the hours worked in each day.

Clarification required:
Is starting time a key to your test of Full vs Not Full (e.g. Full = start before 8:31 AND complete 9 hours), or is No. of hours worked the only critical value (i.e. does working 9 hours no matter when the employee started or ended = Full?)

If Full is a simple test of Hours worked >= 9, then formulas using the COUNTIFS function should return the values required in R6, S6 & T6
Full: R6 =COUNTIFS( $V6:$AB6, ">=" & 9 )
Not Full: S6 =COUNTIFS( $V6:$AB6, ">" & 0, $V6:$AB6, "<" & 9 )
N/A: T6 =COUNTIFS( $V6:$AB6, 0 )


This may not be your entire solution, but hopefully gives you something to work on.