+ Reply to Thread
Results 1 to 2 of 2

Track attendance points for a rolling year with point drop off for perfect attendance

  1. #1
    Registered User
    Join Date
    North Carolina, USA
    MS-Off Ver

    Track attendance points for a rolling year with point drop off for perfect attendance

    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?
    Attached Files Attached Files
    Last edited by 6StringJazzer; 01-29-2020 at 10:44 AM. Reason: fixed typo in title

  2. #2
    Forum Guru
    Join Date
    MS-Off Ver
    Excel 2010/2019

    Re: Track attendance points for a rolling year with point drop off for perfect attendance

    Here is an attendance tracker I worked on some time back that has a point drop off for perfect attendance, although it doesn't include rolling exclusion of points earned.
    One nice feature that I will point out is that it employs row over row recording of attendance. Since there are over one million rows that probably allows for many years worth of attendance to be kept on one sheet, depending on the number of employees.
    The instructions are on the Legend sheet. I imagine that it will not be too difficult to modify the 42 days to a month.
    The point totals per employee are displayed on the totals sheet.
    May not be as pretty as the files attached to post #1, however I feel that it will be easier to work with.
    If this looks like something you would like to pursue let me know and I'll start working on modifying to meet the specifications in post #1.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1