Thread: 30 days- auto deduction; 6 months roll-off: question

Ok, so let me make sure I understand this: Employees get points for violations and, if they've gone 30 days without one, one point is removed. Otherwise, I assume nothing is removed and the new violation points are simply added on, correct?

I'm confused on what happens every six months. Is it six months from the date of each violation that its effects are erased or is it every six months, you completely wipe the slate clean for all employees?

What I'm envisioning now is having your list of violations and then a separate table that totals them for each employee. I'd also have to add a fifth column called Points Remaining. Could you please upload what you have already (with dummy data)? It would help me give you precisely the solution you need with regards to your exact layout. Thanks.

You sometimes were sutracting 1/2 point, and sometimes 1 point.
Your data said A1:A4 which is a column. I assume it is really in A1:D1 which is a row.

This puts points remaining for each offense.
It subtracts 1/2 point every 30 days, and return 0 if over 180 days.
Cell E2: =If(Today()-C2>180,0,D2-(0.5*Floor((ToDay()-C2)/30))) copied down the column.

This puts the total points remaining for all offenses on the row contining the first offense for each name.
Cell F2: =if(Row()<>Match(A2,$A:$A,0),0,SumIf($A:$A,A2,$E:$E) ) copied down the column.


It's possible to put all the names at the top of another column with no empty cells, and alphabetized if desired, with 2 or 3 more helper columns, which you would probably want to hide.

Sorry due to unforeseen circumstances, could not post earlier.
To continue the original poster question: I am attaching an Example.

In this example, you will see:
1) Points roll off 6 months after the incident.
ex. 10/24/2010 ,11/20/2010, 1/22/2011 : no points.
This should reflect in Total points.

2) If no incident in 30 days, one point will be deducted automatically from total points
ex. From 8/28/2011 till 9/30/2011, there is no incident ( call out, late or early release)
Say that agent had 2.0 Total points before, now it became 1.0 Total point as one point is automatically deducted.

Please suggest how to accomplish this automatically.
Currently adjusting the points manually and it is tedious.

Thanks in advance.

Newbie20;

What didn't you like about my solution?