• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Continuous count between occurences

richardking

New Member
Hi to all,

I finally figured out what I need and what I want with the attendance data. So please allow me to post it in this thread.

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.
 

Attachments

  • Attendance.xlsx
    13.2 KB · Views: 21
Hi,

Question is quite clear and simple but solution is not that simple. I am still thinking for one.

I just posted to say good and clear explanation of requirements.

Regards,
Prasad DN
 
oh please, any solution will do as long as I am able to get the desired result. thank you so much for looking into this
 
Hi Richard,

If I am not wrong this is a fresh thread of your old thread, which was answered ( I am sure not fully)

http://chandoo.org/forum/threads/co...etween-occurrences-and-etc.21314/#post-128544

If you see the last request that you made there was to shift the tables to different sheets, which is not a problem.

Than you pointed out a problem that in straight absence it is giving wrong results. I totally agree with it, but I would like to add here something (may be other experts who deals in Excel on a daily basis may not agree with me) when we create some formula we try to see what the OP is requiring, we try our level best to keep the formula as much robust as we can so as it gets fit in all scenario, but as we are not the end users so we can't imagine all the scenarios.

So my question will to upload a sample with maximum scenarios that you can have in your sample data. Like what you expect if there is three continuous absent in a line?

Simply saying that the formula did not gave you the right result, is not enough.

Don't take my words in other way. We all are here to help others and learn new things. Your problems teach us.

Regards,
 
Hi All,

..Was just leaving office when i saw this post. I think i have answered two question, for last sheet4 i need some example:
 

Attachments

  • Attendance.xlsx
    14 KB · Views: 20
Hi Somendra Misra,

Yes I created that thread, but somehow I cant find it. I tried to click "Watched Threads", but its empty. I just realized today how to look for that thread. Im so sorry if this is considered to be a double post, that is not my intention. If ever either of the threads get deleted, kindly keep this current thread instead.

Thank you as well for pointing out that there are some results of the formulas that did not meet the desired result.

For instance, when I will add another day such as February 2 on the attendance sheet (Sheet #1), and on that same day John just made an absence, it is not reflected on sheets 2 and 3. Everyday, I will be adding one day to the attendance sheet, because its an attendance table and it goes on and on, to begin with. Its like a growing table.

Another problem has something to do with sheet 2. It contains 4 columns because of sample purposes, but it should not be limited to 4 columns only. You see, in the scenario that Nancy has incurred another absence in January, there is no place in the sheet to place that. Or for any of the employees that may exceed 4 absences.
 
Last edited:
Can you check the performance of this file.

Note not to keep anything on the attendance sheet, just keep adding rows for dates and columns for employees.

Regards,
 

Attachments

  • Attendance (2).xlsx
    17.9 KB · Views: 23
thank you, finally something that actually works.

with regards to sheet #4 though, I actually plan to create 3 groups (called boxes) that are color coded. Red are for those employees that have incurred more than 1 absences within 10 working days. Blue are for those that made more than 1 absences within 15 working days, and yellow for those that has incurred only 1 absence during the month.
 
thank you, finally something that actually works.

with regards to sheet #4 though, I actually plan to create 3 groups (called boxes) that are color coded. Red are for those employees that have incurred more than 1 absences within 10 working days. Blue are for those that made more than 1 absences within 15 working days, and yellow for those that has incurred only 1 absence during the month.

Will update on this something may be by tomorrow.

Regards,
 
hi faseeh, thank you so much for your effort, but your formulas did not work due to the reasons stated right below your first post. somendra misra was able to provide the right formulas for sheets 1 and 2 though. but still i appreciate your time and effort for looking into my excel question
 
Back
Top