Automated Point Removal For Perfect Attendance

No sample attached.

Whoops! That might be helpful. Thanks for pointing it out, it is there now.

As you need to cross months (Sheets) for your consecutive days count, I suspect VBA is the (likely) solution.

If the Attendance counts are cumulative (as shown in March), then is it not possible to have a single yearly attendance sheet so counting the consecutive days could be done using your current formula?

Doing a yearly attendance tracker is probably the easiest way to go about it, but I don't think my coworkers on first shift would use it. That wouldn't bother me, except that the employees will be the ones to suffer since nobody is keeping track of when they should have a point removed. I was thinking of doing something similar by having an additional sheet that would copy the data from the monthly ranges and lay them out into one long yearly range that nobody would see and then reference that range, but doing that for each of the 100+ employees on my shift alone sounds tedious and time consuming which sort of defeats the whole purpose of automation. Also we have a very high turnover so I will have to add and remove employees on a regular basis. I've seen some other references to VBA but know basically nothing about it. I will have to look into that.

After giving it some thought and having a moment of inspiration, I have decided that having the whole year on one sheet is definitely the way to go. I'm still curious if anyone has a solution for how I originally wanted to do it so I will leave this thread open for a bit longer before marking it solved. Thank you very much for the advice.

No problem: thank you for the feedback.

Re your original format: the only solution I could think of was to record at the end of each month (on month tab) the last consecutive sequence of the month and then on the next month determine the (first) consecutive sequence from 1st of month and add the previous month value and test if > 25 (30?).

I don't how (as opposed to IF) this can be done using a formula: I have a mental block when it comes to using (I don't!) FREQUENCY.

I would happy to look at a VBA function to do the calculation. If this is acceptable, can you post a file with some sample scenarios and clear definition of the rules.

For example, any sequence of 30 days (cells blank and/or valid codes ?) scores a point (including weekends)? so 90 days uninterrupted attendance gets 3 points.

I got the formula I'm using online (can't remember where I found it) and I'm not exactly sure how it works to be honest. I'm not opposed to using VBA, but I'm completely unfamiliar with it. As far a rules this is what I'm trying to do. An employee should receive points for the following: Unexcused absence "U" - 1 point , Tardy <15 minutes "TQ" - 1/4 point , Tardy >15 minutes "TH" - 1/2 point , Left Early "L" - 1/2 point , If the employee is present the cell will be left blank (if necessary it could be represented with a "P"). As an employee misses work their points will add up, but for each string of 30 consecutive days of perfect attendance they should lose one point. If they have 60 or 90 days perfect attendance they would lose 2 or 3 points respectively. If the employee has 30 or more days perfect attendance and then missed a day or more and then has another string of 30 or more days perfect attendance they would lose 2 points. Our work schedule changes on a regular basis, so to start I have included every day of the year. As we progress through the year I will remove the days we don't work. I've attached an example in which we did not work Sundays in January. Sample 1 should have one "Perfect Point", Sample 2 should have 0, and Sample 3 should have 2.

See attached as "prototype" using VBA

Please Login or Register  to view this content.

The macro results are in GREEN table: you will differences on "Perfect Points" compared with your results (Yellow table). My logic is 30 consecutive blanks which sample 3 has.

As it is now a yearly table, aren't previous points redundant? to be replaced by a "Points Balance" of difference between "Reward" and "Penalties"?

The previous points were to avoid resetting everyone's current points. Unfortunately I can only view the file online through Onedrive which won't let me see any formulas, but this looks promising. I will look at it more closely when I return to work. Whether this works or not I greatly appreciate your help with this.

This is almost perfect. Everything works fine except the last few months are not registering. I am still trying to figure out what is causing this. The only other thing I need to figure out is how to keep the total points from going into the negative. If I can fix these two problems it will be ready to take to my boss and begin using. Again, thank you for the help. You have been invaluable to say the least.

When you say the last few months are not registering .... There is a test which exits the calculation if the date is greater than TODAY() i.e. it only processes from 1st January to until TODAY.

Please Login or Register  to view this content.

If you want to process all the year

Please Login or Register  to view this content.

The latter checks for blank cell in column A after 31/12/2016