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?
Bookmarks