smartin,

Good spot on the bonuses after 28 days. For completeness I've modified a
second version so that it uses the maximum consecutive days trigger to
restart the first bonus:

http://www.professionalexcel.com/for...tions_01v2.zip

Whether this makes practical sense, I won't start that debate. I expect you
have to draw the line somewhere on having a max days bonus, then reverting
back to the start. I mean, at some point I would have thought a promotion is
required for 1000s of A days, not just a bonus!

phumpher,

I'm glad you found my comments useful. I've had a quick look at your
solution and it seems ok. Interesting technique using the decimal addition to
avoid counting duplicates. I think your safe in the assumption that no one
will get 1000 'A' days in a row to ruin your calculations ;o)

Regards,

--
----------------------------
Please rate this post if it answers your question.

Thanks,

Chris
http://www.ProfessionalExcel.com


"smartin" wrote:

Good Stuff, Chris!

Please don't take this as a criticism, just an observation...

The only thing that seems to be lacking--and something the OP doesn't
address--is what should happen when an employee has a long winning
streak. E.g., if an employee has straight "A"s for 99 days, they earn
the 28 day bonus, but nothing thereafter.

It might make sense if a 28 day bonus should also reset the counters,
but the OP should provide direction on this.

ProfessionalExcel.com wrote:
Hi,

Instead of explaining my proposed solution, I put together a mock-up that
you can download at the following link:

http://www.professionalexcel.com/for...nctions_01.zip

You can have a look at the forumulas I have used to calculate running daily
counts of consecutive days, taking into account days on leave. The main
lesson is to structure your data in a more managebale way, i.e. my solution
transposes the data and each employee has a column, with each row
representing a day.

I've calculated the count of consectuive days in one column, then added a
further column to make the decision on whether an employee should be paid a
bonus on that given day.

Hope this helps!