Continuous count between occurrences

  • 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.

  • Re: Continuous count between occurrences


    Ok, first, since you have a growing table, let's give it a Dynamic Name.


    Go to the first sheet, then go to Formulas tab, Define Name. Enter a name like: Attendance


    Then enter this formula in the refers to field:


    [COLOR="#0000FF"]=OFFSET('Attendance Sheet'!$A$1,,,COUNTA('Attendance Sheet'!$A:$A),COUNTA('Attendance Sheet'!$1:$1))[/COLOR]


    click Ok.


    Now in Sheet2, cell D1 enter formula:


    [COLOR="#0000FF"]=IFERROR(SMALL(IF(INDEX(Attendance,0,MATCH($C1,INDEX(Attendance,1,0),0))=$C1,ROW(Attendance)-MIN(ROW(Attendance))),COLUMNS($C1:D1))-SUM($C1:C1)-COUNTA($C1:C1),"")[/COLOR]


    confirm it with CTRL+SHIFT+ENTER not just ENTER, then copy it across and down the rest of the table.


    in Sheet3, B1 enter the formula:


    [COLOR="#0000FF"]=MATCH(10^10,INDEX(Attendance,0,1))-MATCH(REPT("z",255),INDEX(Attendance,0,MATCH($A1,INDEX(Attendance,1,0),0)))[/COLOR]


    just hit ENTER, then copy down.


    I am not exactly sure about Sheet 4 requirements. Can you explain further? Why only 2 of the 3 get highlighted? Is there a set number you want highlighted?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • 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.

  • Re: Continuous count between occurrences


    You are very welcome.


    So that is with 10 working days prior to today, right? And by month, do you mean the last 30 days, since you say this is a continuously and monthly growing table?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • 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


    Ok, let's see if this works....


    Go to sheet 4, select the cells to affect with colour, then go to Home|Conditional Formatting|New Rule.
    Select "use a formula to determine which cells to format"
    Enter formula:[COLOR="#0000FF"] =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,29),,MIN(MATCH(10^10,INDEX(Attendance,0,1)),30),1),$A1)=1[/COLOR]
    Click Format and choose Yellow from the Fill tab


    Click Ok, then select New Rule.
    Repeat above with formula: [COLOR="#0000FF"]=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,14),,MIN(MATCH(10^10,INDEX(Attendance,0,1))-1,15),1),$A1)>1[/COLOR]
    Click Format and choose Blue from Fill tab


    Click Ok again and select New Rule
    Repeat with formula: [COLOR="#0000FF"]=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,29),,MIN(MATCH(10^10,INDEX(Attendance,0,1)),30),1),$A1)=1
    [/COLOR]
    Click Fromat and choose Red from Fill tab.


    Click Ok.


    Now check off the "Stop if True" boxes on all 3 rules. And re-order the rules (with move up/down arrows) according to the priority you want the conditions to apply.


    Click Ok.


    Hope that's what you needed.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • 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


    Quote

    Is the formula for the Yellow and Red boxes supposed to be the same?


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


    [COLOR="#0000FF"]=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[/COLOR]


    Quote


    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?


    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.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!