Posts by richardking

    Re: Continuous count between occurrences


    Quote from NBVC;736905

    oops. I think the Red formula should be (must have copied the wrong one here):


    =COUNTIF(OFFSET(INDEX(INDEX(Attendance,0,MATCH($A1,INDEX(Attendance,1,0),0)),MATCH(10^10,INDEX(Attendance,0,1))),-MIN(MATCH(10^10,INDEX(Attendance,0,1))-1,9),,MIN(MATCH(10^10,INDEX(Attendance,0,1)),10),1),$A1)>1


    Thank you.



    Quote from NBVC;736905


    Yes that is why I created the Dynamic Named Range. It allows for your main dataset to be dynamically growing or shrinking, and the formulas will adjust accordingly.


    Wow, that sounds amazing. I didnt know excel can do that. Thank you so much. And by the way, those excel teams of experts are still working on finding the right formula that you were able to produce in a matter of minutes. I think I will be informing them not to bother anymore. Thank you so much for everything

    Re: Continuous count between occurrences


    Is the formula for the Yellow and Red boxes supposed to be the same? And oh, the formulas for sheets 2 and 3 are gonna go on indefinitely, right? It has no limit as to how many days Im going to add to it?

    Re: Continuous count between occurrences


    Its going to be prior to the current date. Like today its January 28, employees who had more than 1 absences within the past 10 working days will belong to the red box, employees with more than 1 absences within the past 15 working days will be on the blue box, and employees with only 1 absence within the last 30 days or within the month will belong to the yellow box.

    Re: Continuous count between occurrences


    Wow! Just WOW! You totally nailed it! You have no idea how long it took for several teams of excel experts to have the right formulas for my desired results. I cant believe how simple and fast it is for you to have this resolved. I am so thankful and happy that you looked into my thread and had the solution right away. I am so grateful and I truly appreciate this. Thank you so much.


    And oh, with regards to sheet 4. 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.

    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.