Need help erasing a cell after a calculation has been applied

[joeyburger]

i have 2 columns Last Violation, and Total Points.... Total points is their total disciplinary points. Every 90 days from their last violation the total points needs to be -2 from the original # and the last violation needs to be erased. I don't know how to get this to display in the same cell and to have the last violation date be erased after the points have issued.

Any help is greatly appreciated

 

[joeyburger]

this is beyond frustrating

 

[taigovinda]

You need a date column for the last violation, right?

Can you post a sample row?

 

[joeyburger]

<table style="border-collapse: collapse; width: 189pt;" width="252" border="0" cellpadding="0" cellspacing="0"><col style="width: 122pt;" width="163"> <col style="width: 67pt;" width="89"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt; width: 122pt;" width="163" height="21">Last Violation Date</td> <td class="xl65" style="width: 67pt;" width="89">Total Points</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border: 0.5pt solid windowtext; background: rgb(141, 180, 227) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">3/10/2009</td> <td class="xl66" style="border: 0.5pt solid windowtext; background: rgb(255, 96, 0) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right">4
</td> </tr> </tbody></table>

 

[taigovinda]

Something like this might be a starting point:

Excel Workbook
	A	B	C	D
1	Last Violation Date	Last Outstanding Violation Date	Gross Points	Net Points
2	3/10/2009	3/10/2009	6	4
3	3/10/2008		6	0
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=IF(D2>0,A2,"")
D2	=MAX(C2-INT((TODAY()-A2)/90)*2,0)

Then say they made a 4-point violation 120 days ago (so their gross is 4 and net is 2). Then today they make another 4-point violation. It seems you might have to copy the *value* of "net points" over to "gross points" - so set gross points to 6 and reset the "last violation date" to today.

 

[taigovinda]

Maybe more like, this:

Excel Workbook
	G	H	I	J
1	Violation Date	Points for Violation	Violation Date to Display	Running Point Total
2	3/10/2008	4		2
3	7/10/2008	2		2
4	12/10/2008	4	12/10/2008	2
Sheet1

Excel 2003

Cell Formulas
Range	Formula
I2	=IF(OR(J2=0,G3>0),"",G2)
I3	=IF(OR(J3=0,G4>0),"",G3)
I4	=IF(OR(J4=0,G5>0),"",G4)
J2	=MAX(H2-INT((IF(ISBLANK(G3),TODAY(),G3)-G2)/90)*2,0)
J3	=MAX(J2+H3-INT((IF(ISBLANK(G4),TODAY(),G4)-G3)/90)*2,0)
J4	=MAX(J3+H4-INT((IF(ISBLANK(G5),TODAY(),G5)-G4)/90)*2,0)

That way you have a record to refer to more than just a running total - and you can just enter each violation when it happens. Then just refer to the bottom line for the current status. You could also set it up to display the bottom line in a given place. So that if you have a chart like this for each of a number of people, you could get a summary page to show all of their names and only the bottom row for each person.