Count Consecutive Empty Cells and Delete non-blank cells by specified number of days.

Hello everyone! I'm new to the forums here. I am having trouble trying to figure out how or if I am able to calculate the following scenarios in my spreadsheet. Our company has a rollover system, and I need to be able to do the following:

• Associates who do not receive a penalty in 90 consecutive calendar days will receive a credit of 0.5 points. However, they will not receive points if the employee has 0 points.

• Each violation will fall off after one calendar year from the date the violation occured.

I have fair knowledge of Excel and I think what I am looking for is a bit too complex for formulas to perform, but I think it can be done in VBA. I have attached the spreadsheet to this post. I have been able to calculate the points I needed using SumIF statements and assign numerical values to Letters/Text using VLOOKUP the way I wanted; just having trouble with these now.

The logic I can think of would be like this ...

• If Count of Consecutive Empty Cells = 90 from a cell containing <Date of Incurence> (possibly from any non-blank cells?), subtract 0.5 from Total Accrued Points,
  Else if Total Accrued Points = 0, add 0.

• If Count of Consecutive Empty Cells = 180 from a cell containing <Date of Incurence> (possibly from any non-blank cells?), subtract 1.0 from Total Accrued Points,
  Else if Total Accrued Points = 0, add 0.

• If Count of Consecutive Empty cells = 270 from a cell containing <Date of Incurence> (possibly from any non-blank cells?), subtract 1.5 from Total Accrued Points,
  Else if Total Accrued Points = 0, add 0.

• If Count of Consecutive Empty cells = 360 from a cell containing <Date of Incurence> (possibly from any non-blank cells?), subtract 2.0 from Total Accrued Points,
  Else if Total Accrued Points = 0, add 0.

If cell containing <Date of Incurence> > 365 days, delete cell containing <Date of Incurence>[/LIST]

Any assistance would be immensely appreciated! Thank you

I managed to do a little research and figure out how to count consecutive blank cells using this array.

=MAX(FREQUENCY(IF(O8:NO8="",COLUMN(O8:NO8)),IF(O8:NO8<>"",COLUMN(O8:NO8))))

Still trying to figure out if this is exactly what I need. The problem is that it counts the entire year. I've received notification from my GM that since the tracking system hadn't been done, any points accrued during April-Mid July will reset.

So for this year, I am thinking maybe the formula should work fine if I just delete the months prior to July..

I decided to test creating a new column for "Credits Earned by Employee" and created a Nested IF formula for the Count of Consecutive days:


=IF(AND(GU6>90,GU6<179),0.5,IF(AND(GU6>179,GU6<269),1,IF(AND(GU6>270,GU6<359),1.5,0)))

Where GU6 for right now is the formula that contains =MAX(FREQUENCY(IF(O8:NO8="",COLUMN(O8:NO8)),IF(O8:NO8<>"",COLUMN(O8:NO8))))

So far, I think the credit counts would work fine in that sense. But I now see the problem. Even removing months prior to July, it's counting all the non-blank cells into the future. I am thinking that some other cell, I have to somehow make the MAX Frequency formula not exceed the present date.