30 Days Perfect Attendance

Hi all,

I monitor my workers attendance.
I use Excel 2007 and are planning to purchase 2010 soon.

Two questions:

1) Perfect attendance for 30 days: we take off 1 point
eg. 7/4/2011 to 8/3/2011
eg. 9/25/2011 to 10/24/2011

If there is no infraction and no points for any 30 day rolling period, I like to take off 1point automatically from the total points, which is 7 in this case.

2) 6 months roll-off:
6 months after the infraction, we remove whatever points are there.
eg. rows 5,6,7,8,9 and 10= have zero total

On 2/18/2012 I like to change 2 points to zero automatically.
Then this will be reflected in the total as 5 points.
(We are not removing the entire 7 points, just the 2 points that show up opposite to the original infraction)

How to do this. Help is greatly appreciated.

I am attaching a sample sheet.

The points is from Rolling total.
Every six months roll-off is, from the day of infraction.

Thank You.

Not sure if I've got it, but you can try this.

In order to automatically delete points on date of Rolloff, I would suggest to add another column to introduce a formula to do that. So in I5 enter formula:

=IF(E5<=TODAY(),0,H5)

copied down.

Then in C1, use that column for your summary which is now formula:

=SUM(I5:I200)-(SUM(INDEX(I5:I200,MATCH(TRUE,INDEX(A5:A200>=INDEX(A5:A200,MATCH(9.999E+307,A5:A200))-30,0),0)):INDEX(I5:I200,MATCH(9.999E+307,A5:A200)))=0)

so you make your manual changes in column H, but column I has the "true" numbers you want based on the logic...

Your request is a lil complicated to understand. Where would you want the points to be shown? What is the rolloff column in the data?

Thank you NBVC,


1) 6 month roll off:
I added another column I- column and the formula works great.

Total in C1 should reflect the total in I column. =sum(I5:I200) will add up the points.

2) Still I don't know how to automatically take away one point for one month perfect attendance:

eg: 7/4/2011- 8/3/2011: no late/sent early/absent. So this is perfect attendance month.

same way: 8/21/2011-9/20/2011: no late/sent early/absent.

So for both perfect attendance, a point for each perfect attendance should come off automatically from I column.
In this example the total then becomes 5 instead of 7.

arlu1201: roll off column is 6 months from the date of occurrence (late/sent early/ absent) which is recorded in A column.

Thank You.