Need help calculating difficult attendance policy

[stryped]

Hi,

I work for a factory with a little different attendance policy. I would like to have a calander for the whole year that supervisors can enter point values for each person in their department. I would like the program to be able to automatically calculate a persons point value at any given time. One of the problems is the points are calculated on a "rolling" basis. The rolling period is 3 months. If a person misses today, that point value is erased exactly 3 months from today.

Here is our policy if that helps:


Monitoring Attendance<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Your attendance will be evaluated on a continuous (rolling) 3-month basis. Points assessed in the first month will be dropped from your record after the third month. This system allows employees to correct their attendance record by improving their attendance during succeeding months throughout the year. (Thus, unless a person has excessive absenteeism over a short period, the assessment of points is merely a way of tracking attendance and should not be seen a punishment in itself.)<o></o>
<o> </o>
<o> </o>
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 5.4pt; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-table-layout-alt: fixed; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="PAGE-BREAK-INSIDE: avoid; HEIGHT: 27.4pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 418.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 27.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=558 colSpan=4>

Example<o></o>​

</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104.6pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=139>

Month<o></o>​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104.65pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=top width=140>

January<o></o>​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104.6pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=top width=139>

February<o></o>​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104.65pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=140>

March<o></o>​

</TD></TR><TR style="mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104.6pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=139>

Points<o></o>​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104.65pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=top width=140>

1.5<o></o>​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104.6pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; PADDING-TOP: 0in" vAlign=top width=139>

.5<o></o>​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 104.65pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=140>

.5<o></o>​

</TD></TR><TR style="PAGE-BREAK-INSIDE: avoid; HEIGHT: 59.8pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 418.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 59.8pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom width=558 colSpan=4>At the end of March the employee has 2.5 points. Since he/she did not exceed the allowed three points the 1.5 points for January are dropped and he/she enters April with 1 point on the record.<o></o>
</TD></TR></TBODY></TABLE>
<o> </o>
Each employee is encouraged to review his/her attendance record with his/her supervisor on a regular basis

Corrective Actions<o></o>
An employee will be allowed three points in any three-month period. If he/she exceeds the three-point allotment, he/she will receive a written warning and be subject to a progressive disciplinary procedure as outlined below:<o></o>
<o> </o>
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 5.4pt; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-table-layout-alt: fixed; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt" vAlign=bottom width=86>

Probation step<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom width=86>

Length of time<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom width=86>

Point limit<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 112.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom width=150>

If point limit is not exceeded<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 112.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom width=150>

If point limit is exceeded<o></o>​

</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=86>

1<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=86>

6 months<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=86>

3<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 112.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=150>Off probation<o></o>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 112.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=150>Go to step 2<o></o>
</TD></TR><TR style="PAGE-BREAK-INSIDE: avoid; HEIGHT: 20.2pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 20.2pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=86>

2<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 20.2pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=86>

6 months<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 64.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 20.2pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=86>

3<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 112.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 20.2pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=150>Return to step 1<o></o>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 112.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 20.2pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=150>Termination*<o></o>
</TD></TR><TR style="PAGE-BREAK-INSIDE: avoid; HEIGHT: 44.05pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 418.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 44.05pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" width=558 colSpan=5>* NASCO's President reserves the right to review a termination decision and retain the employee if deemed appropriate. <o></o>
</TD></TR></TBODY></TABLE>
<o> </o>
Any employee on attendance probation will be required to complete six months work time on the clock. Absences for military duty, leaves of absence, workers’ compensation, etc., will not be counted as time worked in meeting the six-month probationary period.<o></o>
Each employee is responsible for clocking in his/her own time. Clocking in for someone else is prohibited. Any alteration of timekeeping records and/or time clocks is grounds for discharge.

I am not the best at excell but I would appreciate any help from the experts out there! It would help me out a great deal!