Attendance Tracker Where Points Drop Off

I have an attendance tracker right now where it's dated 1/1/15 through 12/31/15. Each box, for each employee, has a drop-down that allows you to select one of 3 incidents - LA, LE or CI for Late, Left Early and Called In - if there was an incident.

The way the point system at work works is that if you're late or leave early you get 1 point, if you call in you get 2 and if you go a pay cycle without any incidents (la, le, ci) you get 2 points back.

Is there a way to define days (pay days) and state that if an employee doesn't have an incident during this time period after their last incident, 2 points are deducted from the overall total? I've got the points to add up correctly, I just can't figure out how to remove them after a certain time.

If there IS a way to do this, is there a way to make it so that if the points equal 10 at any time during the calendar year, the background color changes red or some color?

Sorry! I thought I did! I actually found the spreadsheet elsewhere and have modified it a bit.

I've attached it now and just can't figure out how to make it so that after a payday, points drop off.

Thanks for your help!



attendancetracker.xlsx

On the 1st tab, where it shows the 12 Mos Points, I want that number to be able to fluctuate -- right now you can see the points that have been assigned thus far. I'm trying to be able to specify days so that if a person doesn't get any points during that time, 2 points are deducted from their overall total. They're also allowed to bank up to 5 points (meaning the 12 Mos Points number could go as low as -5).

Can I specify ranges of days and if a point is accrued during that time, 2 points are taken off?

If need be, I don't mind there being an extra box that takes the 12 Mos Points and calculates something else to show the points after points are awarded back for "good behavior".

I found some examples on how to take points off after 90 days but I need it different than that.

Anyone know of any formulas that can be used for what I'm looking to do?

Anyone have any ideas? I still can't figure out how to take off numbers from a certain date range / time period.