Count Consecutive Days (with rule associated)

Hi there guys

I need some help. I have inherited an existing excel based timesheet that I have been adding automation to and have come across a situation that has stumped me. I need to calculate an allowance that is applicable after 5 consecutive days of travelling (on company business). The timesheet is based on a monthly calendar format and text is used to identify the allowance "ONV". The allowance is automatically applicable on Saturday and Sunday so I need to work on a solution for Monday to Friday with the allowance being applicable from day 6 onwards. If there is a break in the travel the rule must reset itself and start again.

Can anyone assist?

Thank you in advance DUNC78.

A sample file would be helpful, showing suitable examples of normal/exceptional cases.

John

Please find attached. You will see on cell D24 of any of the sheets (monthly attendance) the catch all formula(=COUNTIF(C15:L20,"ONV")/2-5) however this not correct as per the rules that have now been implemented. I am normally pretty proficient with Excel but this one is perplexing me.

Maybe it is the fact I am just back from annual leave and my head is not quite back at work yet.



Cheers

DUNC78