richardking
New Member
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 included. The numbers in gray (big font) are not part of the actual file, they are just there for reference for each of the charts/tables they are attached to. Kindly check the sample file
Chart #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.
Chart #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 20 days straight before he had made 1 absence. Then another 7 days straight before he incurred another absence. Thus, you can also say that chart #2 is the number of consecutive days an employee has been present between absences.
Chart #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.
Chart #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.
Im 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
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 included. The numbers in gray (big font) are not part of the actual file, they are just there for reference for each of the charts/tables they are attached to. Kindly check the sample file
Chart #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.
Chart #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 20 days straight before he had made 1 absence. Then another 7 days straight before he incurred another absence. Thus, you can also say that chart #2 is the number of consecutive days an employee has been present between absences.
Chart #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.
Chart #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.
Im 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
