Hello,
I have been doing some reading on a rolling system to add and deduct the points as our employee attendance changes.
We have a point system and the goal of each employee is to have the least amount of points as possible, because once they reach a certain amount of points they will receive a corrective action.
It is a rolling 12 month. The corrective action levels are 6, 9, 12 and 15 points.
We are now thinking about adding a perfect attendance portion where for each calendar month of perfect attendance, one point will drop off (the oldest one for that 12 month rolling period).
Ex. Let's say Bob has currently 7.5 points as of 1/29/20, he then has a perfect month of attendance for the entire month of February, on March 1st one point will drop off and he will be at 6.5 points.
I would also like to add a feature where once you reach the first day of a given month, the points from that month of last year will drop off. So instead of dropping off as you reach each day 12 months later, all points gathered in a particular month of last year will drop off on the first of the same month in the current year.
Ex. Jane was absent/tardy three times in January of 2019 (4th, 17 and 20th) for a total of 3.5 points; on January 1, 2020, all absences from January of 2019 drop off and her attendance record is now less 3.5 points.
By rolling, I mean a formula that will calculate continuously over month after month, year after year.
I have found two spreadsheets and I have no idea how to adjust them to my needs.
The first one "Attendance Tracking -full year" - would be nice because it tracks multiple years. How do I add different codes to the validation table? Like no call no show or FMLA?
Second one "Attendance template 2020" - has an easy to read format, which would be great to share with the supervisors, but it does not have the year over year tracking.
How can I combine the two?
Bookmarks