Hi all,

I am new and got a difficult project at work. I amdesperately trying to solve this issue:

I have a line of someone's roster. 31 days. I am to build a model that will see this roster, and if it sees a 'block' of consecutive blank cells, it will count how many are there in this 'block' and assign a number of day off (that will be specified by the user), but let it be 1 for starters.

So it may look like:

[] [] [] WORK WORK WORK WORK [] [] [] [] [] [] OFF OFF OFF WORK [] [] [] [] [] WORK WORK WORK WORK etc.
In the above example, the formula/VBA should look at it in this way: 3 blanks - (no action needed), 4 WORK (no action needed, 6 BLANKS (assign "1" to a specified cell (at the end of the line), etc. So it's like scanning through the row, finding consecutive blanks and with this condition, spit out a number of days off i will need to give someone extra.

I have already gathered and used an array formula: ={MAX(FREQUENCY(IF(C5:AG5="",COLUMN(C5:AG5)),IF(C5:AG5<>"",COLUMN(C5:AG5))))} to count MAX number, but this gives me only the biggest number of consecutive blanks.

Also, I used an array formula to count how many blocks of >5 blank cells are there:
={SUM(0+(FREQUENCY(IF(C4:AG4="",COLUMN(C4:AG4)),IF(C4:AG4<>"",COLUMN(C4:AG4)))>5))}
but again, this only gives me the number of such blocks.

Pretty desperate for this and don't know how to tackle it ;(