Remove points after period of time.

I have a spreadsheet that has a calendar on one sheet - 1/1/15 to 12/31/15 - and each row/column has a drop down where you can pick if the person called in, was late or left early. On the 1st sheet the points are calculated based on what options were selected on the 2nd sheet. What I'm looking to do is make it so that IF, and only if, after 1 pay period (2 weeks) they don't call in, aren't late and don't leave early, they get 2 points taken off their total points, on the 1st sheet.

I've attached a copy so you can look at it.

Any help would be greatly appreciated. If you have questions, please don't hesitate to ask. I've been trying to figure this out for a while and can't seem to come up with a formula that works.

What about subsequent pay periods? Do we deduct for those as well?

Hopefully this isn't against the rules as I'm about to mention another software....we had charts that we always needed updates for last week (for example). It was so frustrating to change the date ranges all the time. So we bought Tableau. All of our data is still in Excel, except our charts which are in Tableau. They have so many more time range options that saved us so much work time that it was well worth the purchase.

That being said, I don't know if that's the exact problem or if purchasing software is even an option...but just thought I'd throw the idea out there.

---

If you found this post to be helpful, or if it at least brought a smile to your face, please say "Thank You" by clicking Add Reputation. Thank you!

The points would deduct after one pay period which is 2 weeks. I don't mind setting a start date of the 2 weeks - it can be the first pay date of the year. The main thing is to deduct 2 points if they've gone 1 pay period without any "incidents" as we call them. The 2 weeks would essentially be ranges that change on a continuous basis so the spreadsheet would know when pay periods ends, in essence, and would be able to deduct points.

As for Tableau, I've never heard of it but I'll check it out.

Yes the points would deduct and the person can "bank" up to 5 points - that's to say their point count can go as low as -5. So yes, if they go 5 paydays without incident they'd accumlate 10 points, until they get to -5.

Any good sites you can recommend where I can get some good information on how to possibly go about this?