Attendance Tracking Help

[snwalke]

Hello all!

I'm in the process redoing an attendance tracking spreadsheet for my company and have one into one issue. First, let me run through how we use our point system.
Call in- 1 pt
No call no show(NCNS)- 5 pts
Left early (worked at least half shift) - 0 pts
Left early (worked less than half shift)- 0.5 pts
Excused absence- 0 pts
If an employee goes 6 weeks (42 days) without an absence their point total goes down 1 point. I am wondering what formula I could use so that it calculates the "perfect attendance" points based on today's date if they have had no more absences or the date of the employee's last absence. The problem I previously ran into is if they had gotten 2 points taken off their total for perfect attendance and then called in- it would bring them back to their original total rather than giving them credit for the 12 weeks of going without an absence.

I would appreciate any and all help