This needs a userdefined function (UDF)
Enter the following code in a module and the following in Row 16 to the right =overhours(A16:FQ16) and =underbreak(A16:FQ16)
Function OverHours(Data As Range)
rw = Data.Row
For i = 6 To 170 Step 4
If IsNumeric(Cells(rw, i)) Then
hrs = Cells(rw, i + 1) - Cells(rw, i)
If hrs < 0 Then hrs = hrs + 1
If hrs > 0.5 Then
x = x + 1
End If
End If
Next
OverHours = x
End Function
Function UnderBreak(Data As Range)
rw = Data.Row
For i = 10 To 170 Step 4
If IsNumeric(Cells(rw, i)) And IsNumeric(Cells(rw, i - 3)) _
And Cells(rw, i) > 0 And Cells(rw, i - 3) > 0 Then
hrs = Cells(rw, i) - Cells(rw, i - 3)
If hrs < 0 Then hrs = hrs + 1
If hrs > 0 And hrs < 11 / 24 Then
x = x + 1
End If
End If
Next
UnderBreak = x
End Function
For testing purposes, this will check rows 16:55 on the sheet, highlight issues and return a result
Sub Test() Dim rw As Long
Cells(16, 1).Resize(40, 170).Interior.ColorIndex = xlNone
For rw = 16 To 55
OH = OH + xOverHours(rw)
UB = UB + xUnderBreak(rw)
Next rw
MsgBox "OverHours: " & OH & vbCr & "Underbreak: " & UB
End Sub
Function xOverHours(rw As Long)
For i = 6 To 170 Step 4
If IsNumeric(Cells(rw, i)) Then
hrs = Cells(rw, i + 1) - Cells(rw, i)
If hrs < 0 Then hrs = hrs + 1
If hrs > 0.5 Then
Cells(rw, i).Resize(, 2).Interior.ColorIndex = 3 'Debug
x = x + 1
End If
End If
Next
xOverHours = x
End Function
Function xUnderBreak(rw As Long)
For i = 10 To 170 Step 4
If IsNumeric(Cells(rw, i)) And IsNumeric(Cells(rw, i - 3)) _
And Cells(rw, i) > 0 And Cells(rw, i - 3) > 0 Then
hrs = Cells(rw, i) - Cells(rw, i - 3)
If hrs < 0 Then hrs = hrs + 1
If hrs > 0 And hrs < 11 / 24 Then
Cells(rw, i - 3).Resize(, 4).Interior.ColorIndex = 4 'Debug
x = x + 1
End If
End If
Next
xUnderBreak = x
End Function