Creating a spreadsheet where, if you miss a day from work it counts as 1 missed day. ex. if you missed 1/21/2008 count as a 1, then any other days missed add to the total. After a 12 month rolling period that missed day would come off you record.

I have come up with this on one spreadsheet:

=SUMPRODUCT((A13:A200>=$M$2)*(B13:C200="ABS")) + (SUMPRODUCT((A13:A200>=$M$2)*(B13:C200="Tardy"))/2)

M2= =IF(ISERROR(DATEVALUE(MONTH(M1) & "/" & DAY(M1) & "/" & YEAR(M1)-1)),today-365,DATEVALUE(MONTH(M1) & "/" & DAY(M1) & "/" & YEAR(M1)-1))

M1= today()

For some reason the above sumporduct formula will remove the day missed on 1/22/2009.

On another spreadsheet i have:

=SUMPRODUCT(($A$23:$A$200>$M$2)*($B$23:$B$200="Absence"))+(SUMPRODUCT(($A$23:$A$200>$M$2)*($B$23:$B$200="PTO Unplanned")))

M2= =IF(ISERROR(DATEVALUE(MONTH(M1) & "/" & DAY(M1) & "/" & YEAR(M1)-1)),today-365,DATEVALUE(MONTH(M1) & "/" & DAY(M1) & "/" & YEAR(M1)-1))

M1= today()

This sumproduct formula will rrmove the missed day on 1/21/2009.

What we are debating is which 1 to use, do we have it remove the missed day on 1/22/2009 or 1/21/2009?

Any suggestions on which formual to use?