Help creating a formula to count blank cells in row since last infraction

[ceric5683]

Hello! I require a bit of help creating a formula for my attendance spreadsheet. We implemented a policy that states "if you have too many infractions, you will go on probation for 30 days, AS LONG as you don't have any infractions during those 30 days. If you do, however, the count starts back at 0.
I don't want to sit and count, especially spanning over months. Can you help me with a formula? The spreadsheet looks like this:
MONTH OF SEPTEMBER


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]EMPLOYEE NAME[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]SMITH[/TD]
[TD]L[/TD]
[TD][/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JONES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]UEX[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHNSON[/TD]
[TD][/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]

SO I NEED A FORMULA TO SAY ITS BEEN 2 DAYS SINCE SMITH HAS HAD INFRACTIONS, 1 DAYS FOR JONES AND 0 DAYS FOR JOHNSON...HOW DO I DO IT?

 

[kweaver]

I think I found 2 ways:

=6-LOOKUP(2,1/(B2:F2<>""),COLUMN(B2:F2))

and

=5-AGGREGATE(14,6,(COLUMN(B2:F2)-COLUMN(B2)+1)/(B2:F2<>""),1)