Continuous count between occurrences

[richardking100]

Hi to all,

I really need help in writing formulas for the attendance of my employees.

What I want is really simple, hope I can also explain it simply.

Attached is a sample of how I want it to appear, and for sample purposes I only put 3 employee names to make it easier. The first ever working day is set on the 1st of January, weekends not included. Kindly check the sample file

Sheet #1 is the table of attendance for the 3 employees for the month of January. Dates that contain an employee name are the days that an employee has incurred an absence.

Sheet #2 is the table for the straight number of days an employee has been present before it has incurred an absence. Take the attendance of John for instance, he has been present for 14 days straight before he had made 1 absence. Then another 4 days straight before he incurred another absence. In the case that an employee has incurred another absence on January 30th, like Nancy for example, the number 3 will appear on cell H3 indicating that Nancy just had 3 straight days of being present before making another absence. Thus, you can also say that sheet #2 is the number of consecutive days an employee has been present between absences.

Sheet #3 is the table for the actual and on-going count that an employee has been present since its last absence. Like for John who has been present for 2 days straight since his last absence, Mark has been present for 1 day since his last absence, and Nancy has been present for 4 days straight since her last absence. These numbers are going to continue its count come February, then March, and so on. Thus, on February 1 those numbers will become 3, 2, and 5, assuming they are all present on that day. The only time that these numbers will reset is when an employee has incurred an absence. This part is a little tricky for me, since everyday we will be adding 1 day as we move forward, so the count will also increase by 1 for each employee as long as they wont incur an absence.

Sheet #4 is simply a table that tells who has the most absences from work within the shortest interval, which in this case is Nancy and Mark who are both on the top box (red). Also, having many absences does not mean an employee will be put on the top box, especially on the long run. But rather the employee who has many absences within short intervals, like having incurred an absence or absences once or more every 10 days.

m not sure how its going to be done, but most likely the formulas are going to be on the cells that contains numbers in bold and purple font.

I know its strange, but this is what I need and it is important for me to get those formulas. So I really need your help guys and I will truly appreciate it :D

Also, is there a way to hide empty cells and clump up filled cells.