Attendance Tracking with Occurrence Points over 6 Month Rolling Period

[kaeathanasiou]

Hello,

I have created an attendance tracker with each month being its own sheet and a Summary sheet that tracks the occurrence (unexcused, no-call no-show, etc.) points accumulated over 6 months. The summary sheet consists of two summaries - one for the first half of the year and another for the second half. What I actually need though is something that generates the occurrence points over a 6 month rolling period with the points falling off after 6 months. The points vary from .5 to 3. I have no idea where to begin with this task and any advice is appreciated.
Attendance Tracker.xlsx

[Estevaoba]

Hello. Welcome to the forum.

I have listed the sheet names in column U (for example) and created a dynamic range named Sheets with this formula:

=INDEX(Summary!$U$2:$U$13,MATCH(MONTH(TODAY())-5,ROW(Summary!$Z$1:$Z$12),0)):INDEX(Summary!$U$2:$U$13,MATCH(MONTH(TODAY()),ROW(Summary!$Z$1:$Z$12),0))

Then I use this formula in N9, copied across to Q9 and down:

=SUM(INDIRECT("'"&Sheets&"'!"&ADDRESS(ROWS($Z$1:$Z6),COLUMNS($A$100:D$100),4,1)))

This formula works from July to December only, as the dynamic range spans back 6 months, so it will result error when current month is June or earlier. For that part, you could stick to the first summary table, with those formulae you have entered.

Please run some test and let us know how it goes.

Good luck!

[kaeathanasiou]

Thank you, I plan to work with this a little more to better understand. What you have provided is very helpful.

[kaeathanasiou]

Thank you! This is very helpful, I will run more tests to further develop my understanding.

[Gregb11]

I understand this solution may not work for you, but it allows for a lot more flexibility.
There are just 2 sheets. 1 sheet is a table, where you enter the Employees name when they're absent, enter the date, and the reason code (it's a drop-down so all you need to do is enter the first letter).
The other Sheet is the summary sheet. The results "SPILL" dynamically depending on the dates you're reporting on. The default dates (cells in gray) are a rolling 6 months. But you can enter any override dates you want in the cells highlighted in yellow (maybe to see a specific month). It will only show those employees that had any absences that time period.

[kaeathanasiou]

How can I expand the table to accommodate for 20 employees (in the unlikely event we have 20 employees each having occurrences) and not include vacation or sick days in the total as these are considered excused absences and no points are accumulated for these types of leave?

[Gregb11]

You're welcome, and thanks for the rep!

[Gregb11]

That's the beauty of the Excel Table and the Summary sheet - you just add the employee to the table and the table expands and the Summary sheet will automatically include them. To not include vacation or sick days in the Total, use this formula in cell A9 on Summary page:

=LET(Names,UNIQUE(FILTER(Table1[Name],(Table1[Date]>=B5)*(Table1[Date]<=B6),"None")),
Vacation,COUNTIFS(Table1[Reason],B8,Table1[Name],Names,Table1[Date],">="&B5,Table1[Date],"<="&B6),
Sick,COUNTIFS(Table1[Reason],C8,Table1[Name],Names,Table1[Date],">="&B5,Table1[Date],"<="&B6),
Unexcused,COUNTIFS(Table1[Reason],D8,Table1[Name],Names,Table1[Date],">="&B5,Table1[Date],"<="&B6),
NCNS,COUNTIFS(Table1[Reason],E8,Table1[Name],Names,Table1[Date],">="&B5,Table1[Date],"<="&B6)*3,
Tardy,COUNTIFS(Table1[Reason],F8,Table1[Name],Names,Table1[Date],">="&B5,Table1[Date],"<="&B6)/2,
LateReturn,COUNTIFS(Table1[Reason],G8,Table1[Name],Names,Table1[Date],">="&B5,Table1[Date],"<="&B6)/2,
Total,Unexcused+NCNS+Tardy+LateReturn,
HSTACK(Names,Vacation,Sick,Unexcused,NCNS,Tardy,LateReturn,Total))

I'll attach the workbook with this change as well.

All names that have an occurrence within the date range will automatically show up on the Summary Page. If you have a situation where they are not, please attach the workbook so I can see what's happening.

[kaeathanasiou]

Thank you so much! I understand. This will work great for what I am trying to achieve.