Excel 2007 : how to count consecutive periodes of sickleave for a month

Hey Guys,

I need a formula for the following;

I have to build a time registration for someone i know.
It is almost finished but i'm having some difficulty with the following:

For every single workday i have created a checkbox (boolean, linked to a cell) which you can check if you have a sickday.
So if you are sick for four days in a row, you need to check four seperate checkboxes. (one for every day)

now comes the tricky part.

How do i count the four sick days as a whole?
Every sickleave starts with a waiting day which does not count as a sickday.

So if you are sick for four days, you get paid for three of your sickdays because you have one waitingday (the waiting day is always the first day of your sickleave)

If you are sick for just one day, you get nothing because the first day is always a waiting day.


what i want is the number of times an employee is sick in a month.
i guess it only takes one working day to distinguish the sick periodes from the working periodes.

lets say an employee has been sick three different times in a month,

first time, three days sick (include waiting day)
second time, two days sick (include waiting day)
second time, four days (include waiting day)

that makes, 3+2+4 = 9, minus 3 waiting days = 6 days of sickleave in total.

i want that 6 days of sickleave but how do i get it?

i think i first need to get the sickleave periodes, (but how?)
after that i need to subtract the waitingdays.
and then i get my six days of sickleave but how do I do that?

I hope anyone can help me!

Thnx in advance!