Counting Attendance in a Rolling Period

[7naumankhan7]

So far I've been unable to get help with this and am hoping to find it here.

I am using the following formula to calculate points for the first 90 day period(first 90 rows).

IF(H4="","",IF(W4="","",IF(H4>0,IF(W4>0,IF(B4-$B$4<=90,SUM($H$4:H4))))))

Beginning with the 91st row(actual row 94) here's the formula: IF(H94="","",IF(H94>0,IF(B94-B4<=90,SUM(H5:H94),IF(B94-B4>90,SUM(H5:H94)))))

All this works perfectly to count any last 90 days. However, if the limit is exceeded then it is considered a violation. How do I reset the count after each violation and begin counting again?

 

[pbornemeier]

What data is held in each column (H, W, B) mentioned?
What is the value of the limit for a violation? What cell is it stored in?
If the limit was 3 and you had 1 point on each day 10, 11 and 80, When the violation occurs on day 80 do you reset the point count to 0 at day 81?

Would it work to have an additional column (I) included in the sum and when the violation occurs on day 80 with 3 points accumulated, put -3 in that column so the points that caused the violation are cancelled out?

=IF(H94="","",IF(H94>0,IF(B94-B4<=90,SUM(H5:H94),IF(B94-B4>90,SUM(H5:I94)))))

 

[7naumankhan7]

What is the value of the limit for a violation?
12
What cell is it stored in? It's formulas, see below.
If the limit was 3 and you had 1 point on each day 10, 11 and 80, When the violation occurs on day 80 do you reset the point count to 0 at day 81? yes, but the day is to be 91.

Would it work to have an additional column (I) included in the sum and when the violation occurs on day 80 with 3 points accumulated, put -3 in that column so the points that caused the violation are cancelled out? Hmm....Hadn't thought of that....could you explain a little, please?

Sorry, no need for H4 in that formula:
A=Day; B=Date; C=Event Name (absence, tardy); D=Lost Production (Hours); E=Documentation Provided (Y/N); F=Excused (Y/N); G=FML (Y/N); H= individual points for each event (has formula "=W4); I=Total Points (refers to formula in N5);

J = <TABLE style="WIDTH: 272pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=363 border=0><COLGROUP><COL style="WIDTH: 272pt; mso-width-source: userset; mso-width-alt: 13275" width=363><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 9pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 272pt; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Verdana; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=363 height=17>IF(I4="","",IF(I4<=12,"",IF(I4>12,"Violation")))</TD></TR></TBODY></TABLE>
K= Reason
L/M=Comments

N= In Rows 4 through 93 to include the 4th row for the first 90 days<TABLE style="WIDTH: 271pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=361 border=0><COLGROUP><COL style="WIDTH: 271pt; mso-width-source: userset; mso-width-alt: 13202" width=361><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 271pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=361 height=17>IF(W4="","",IF(W4>0,IF(B4-$B$4<=90,SUM($H$4:H4))))</TD></TR></TBODY></TABLE>N= In Rows 94 through 5000
<TABLE style="WIDTH: 260pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=346 border=0><COLGROUP><COL style="WIDTH: 260pt; mso-width-source: userset; mso-width-alt: 12653" width=346><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 260pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=346 height=17>IF(W94="","",IF(W94>0,IF(B94-B4<=90,SUM(H5:H94),IF(B94-B4>90,SUM(H5:H94)))))</TD></TR></TBODY></TABLE>
O=<TABLE style="WIDTH: 278pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=371 border=0><COLGROUP><COL style="WIDTH: 278pt; mso-width-source: userset; mso-width-alt: 13568" width=371><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 278pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=371 height=17>IF(E4="","",IF(F4="","",IF(E4="Yes",IF(F4="Yes","Yes"))))</TD></TR></TBODY></TABLE>
P=<TABLE style="WIDTH: 262pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=349 border=0><COLGROUP><COL style="WIDTH: 262pt; mso-width-source: userset; mso-width-alt: 12763" width=349><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 262pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=349 height=17>IF(E4="","",IF(F4="","",IF(E4="No",IF(F4="No","No"))))</TD></TR></TBODY></TABLE>
Q=<TABLE style="WIDTH: 267pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=356 border=0><COLGROUP><COL style="WIDTH: 267pt; mso-width-source: userset; mso-width-alt: 13019" width=356><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 267pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=356 height=17>IF(E4="","",IF(F4="","",IF(E4="Yes",IF(F4="No","No"))))</TD></TR></TBODY></TABLE>
R=<TABLE style="WIDTH: 273pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=364 border=0><COLGROUP><COL style="WIDTH: 273pt; mso-width-source: userset; mso-width-alt: 13312" width=364><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 273pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=364 height=17>IF(E4="","",IF(F4="","",IF(E4="No",IF(F4="Yes","Yes"))))</TD></TR></TBODY></TABLE>
S=<TABLE style="WIDTH: 97pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=129 border=0><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=129 height=17>IF(O4="Yes",O4,"")</TD></TR></TBODY></TABLE>
T=<TABLE style="WIDTH: 89pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=118 border=0><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=118 height=17>IF(P4="No",P4,"")</TD></TR></TBODY></TABLE>
U=<TABLE style="WIDTH: 90pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=120 border=0><COLGROUP><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 90pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=120 height=17>IF(Q4="No",Q4,"")</TD></TR></TBODY></TABLE>
V=<TABLE style="WIDTH: 94pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=125 border=0><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=125 height=17>IF(R4="Yes",R4,"")</TD></TR></TBODY></TABLE>
W=<TABLE style="WIDTH: 176pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=235 border=0><COLGROUP><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8594" width=235><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 176pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=235 height=17>IF(G4="Yes","",IF(S4="Yes","",IF(V4="Yes","",IF(D4="","",IF(D4<1,2,IF(D4>=1,IF(D4<=3,3,IF(D4>3,5))))))))</TD></TR></TBODY></TABLE>


If it is easier, I can email the file to you.

I appreciate your reply to my post.

Thanks
Nauman Khan

 

[7naumankhan7]

If the limit was 3 and you had 1 point on each day 10, 11 and 80, When the violation occurs on day 80 do you reset the point count to 0 at day 81?

Actually, I am able to drop off the oldest ones without any problem, violation or no violation, using a formula in N. The problem is if a violation occurs on day 30, and the person is absent again on day 32, this should be counted as first absence. All the previous ones should be ignored.