Attendance Tracking Help

This proposal adds columns to the table for each person listed on the Totals sheet.
The column headers are added by copying the list from the Totals sheet and pasting transpose.
The columns show the points that have been reduced due to multiples of 42 days attendance between absences and are populated using:

Formula:

Please Login or Register  to view this content.

G1:AP1 shows the number of points excused by multiples of 42 days attendance between the last absence and today and is populated using:

Formula:

Please Login or Register  to view this content.

G2:AP2 shows the total reduction of points using: =SUM(SUMIF(Table1[DEBBRA],"<="&9.99^307),G1)
G3:AP3 shows the total points using: =MAX(0,SUM(SUMIFS(Table1[[Point]:[Point]],Table1[[Name]:[Name]],G4),G2))
On the Totals sheet the Point Total column is populated using: =INDEX('Day by Day'!G$3:AP$3,MATCH(A5,Table1[[#Headers],[DEBBRA]:[STEPHANIE2]],0))
Let us know if you have any questions.

Assuming that you do not want to count any days marked EX:
1. Amend the formula in G1:AP1 to read:

Formula:

Please Login or Register  to view this content.

2. Amend the formula in G4:AP37 to read:

Formula:

Please Login or Register  to view this content.

Another option might be to add "FMLA" as a new code in which case you may replace instances of "EX" in each of the formulas with "FMLA".
Let us know if you have any questions.

You're definitely correct.
1. Add FMLA to the list of codes on the Legend sheet
2. Amend the formula in the Sum column to read: =SUM(COUNTIF(C5,{"CI","LEH","LE","NCNS","EX","FMLA"})*{1,0,0.5,5,0,0})
3. Amend the formula in G5:AP37 to read:

Formula:

Please Login or Register  to view this content.

4. Amend the formula in G1:AP1 to read:

Formula:

Please Login or Register  to view this content.

Selecting cell and using the Evaluate Formula feature note that in KYLIE's has 45 days between "CL" absences 2/4 to 3/21 however 4 of those days are marked "FMLA" and thus not counted. Since only 41 days are counted no points are reduced.
If you change KYLIE's last CL absence to 3/22 then KYLIE's day's between becomes 42 (i.e. 46 - 4) so that one point is reduced.
Likewise selecting cell and using the Evaluate Formula feature note that between KYLIE's last "CL" absence and today there are 47 days and no "FMLA" absences are included, so one point is removed.
Let us know if you have any questions.

Good catch. Don't worry about asking questions, I'll be glad to answer if I can.
I modified the formula in G5 to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

In the process of working out kinks the formulas may have been changed from the ones in the file attached to post #9. If that is the case please upload a sample of the current workbook.
Let us know if you have any questions.

Let me see if I understand this correctly.
Debbra has a CI on 1/8 which is 1 point.
The next CI is on 2/14 which is another point. Since there were only 37 days between 1/8 and 2/14 the 1 point is added so Debbra now has 2 points.
Debbra has an LE on 5/10 which is half a point. Since there were 85 days between 2/14 and 5/10 both the previous 2 points are voided.
Debbra has an LE on 8/5 which is half a point. Since there were 87 days between 5/10 and 8/5 the previous half point is voided.
Since there are only 25 days between 8/5 and 8/30 Debbra should have half a point in the Total Pts. cell.
Is that correct?

I believe that the following will work:
1. Change the formula for column E to read:

Formula:

Please Login or Register  to view this content.

2. Change the formula for G5:AP40 to read: =IF(OR($C5="FMLA",$C5="EX",$B5<>G$4),SUM(G4,0),MAX(0,SUM($D5,G4)-ROUNDDOWN($E5/42,0)))
3. Change the formula for G3:AP3 to read: =INDEX(Table1[DEBBRA],COUNT(Table1[[Date]:[Date]]))
Let us know if you have any questions.