Automated Point Removal For Perfect Attendance

[jsamp752]

Hi everyone, I am very much a newbie, and could use some help. I am putting together an attendance tracker for where I work. I have included a sample spreadsheet to show what I'm talking about. Each month is a separate sheet. In my sample February is how attendance used to be tracked and March is how I want to track it. Doing it the old way everyone's points had to be added up manually. I have made it automated so that when I add a 'U', 'E', or whatever letter applies it will automatically total up how many points that person has. I even have the total point column set up to add the points from the previous month. So far everything is working exactly as intended, but this is where things get tricky. We have a policy in place that if an employee has 30 consecutive days of perfect attendance we will remove one point from their record. I know that nobody is going to manually count how many days each employee has come to work in a row, so this, more than anything else, needs to be automated. The red cells AJ9, AJ10, and AJ11 show the current formula I am using. It is looking at a range of cells for each employee in March. If there are 25 consecutive cells with no data in them it will display a 1 and if the employee did not have 25 days of perfect attendance it will display 0. That amount will then be subtracted from the employees total points. This would work fine if the employee had to have perfect attendance for a calendar month, but that's not the case. The 30 consecutive days can start one month and finish the next, which means this formula needs to be able to do the same. I can not figure out how to make it look at a range of cells consisting of multiple ranges across multiple sheets. To be exact, in this sample I want cell AJ9 in March to look at February B9 through Z9 and March B9 through AC9 and if there are 30 consecutive cells with no data it should add 1 to March AJ9. I have been looking for an answer to this problem for a while now and any help at all would be greatly appreciated by both myself and my employees. Thanks.

[JohnTopley]

No sample attached.

[jsamp752]

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

[JohnTopley]

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?

[jsamp752]

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.

[jsamp752]

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.

[JohnTopley]

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?).

[jsamp752]

OK, so this is what I've got. I did end up going the route you suggested. I ran into a couple problems along the way but they have all been things I could work around. For instance, my employer uses Excel 2003 which only has 256 columns. I ended up just putting the dates in rows and employee names in column. It's the opposite of what everyone wants but it works. The only issue I have now is the formula I'm using to calculate if someone has been present for 30 consecutive days, it should remove one point. As it stands now it will only ever remove one point instead of removing a point for each string of 30 days. How can I make it remove one point for each instance of 30 days perfect attendance? This is the array formula I am currently using: =SUM(IF(FREQUENCY(IF(C3:C368="",COLUMN(C3:C368)),IF(C3:C368<>"",COLUMN(C3:C368)))>=30,1)) It is located in the cells along row 376. I've attached a sample copy of what I have so far. Please note the zeros are just place holders to keep the formula from subtracting points for days not yet worked.

[JohnTopley]

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.

[jsamp752]

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.

[JohnTopley]

See attached as "prototype" using VBA

Sub Perfect_Points_X()

Dim inArr() As Variant
Dim outarr() As Variant

Dim r As Long
Dim c As Long
Dim n As Long
Dim nOK As Long


Dim lastrow As Long
Dim lastcol As Long

Dim inRng As Range
Dim outRng As Range

Dim ws1 As Worksheet
Dim Today As Date

Set ws1 = Worksheets("Sheet1")

Application.ScreenUpdating = False

oRow = 365
Srow = 3
sCol = 3

ws1.Activate

With ws1
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
    Set inRng = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))
    inArr = inRng
    
    ReDim outarr(1 To 7, 1 To lastcol)
    
    For c = 1 To lastcol
     outarr(1, c) = .Cells(oRow, c)
    Next c
    
    For r = 2 To 7
        outarr(r, 2) = .Cells(oRow + r - 1, 2)
        For c = 3 To lastcol
        outarr(r, c) = 0
        Next c
    Next r
    
'    Set inRng = .Range(.Cells(oRow, 1), .Cells(oRow + 6, lastcol))
'    outarr = inRng
    
    Today = Format(Now(), "dd/mm/yyyy")

    
    For c = sCol To UBound(inArr, 2)
    
        nOK = 0
        nPts = 0
        
        For r = Srow To UBound(inArr, 1)
            
            If inArr(r, 1) = "" Or inArr(r, 1) > Today Then Exit For
        
            If inArr(r, c) <> "" Then
            
                aCode = inArr(r, c)
                
                Select Case aCode
                    Case "U"
                       pPoints = 1#
                       outarr(2, c) = outarr(2, c) + pPoints
                    Case "TQ"
                       pPoints = 0.25
                       outarr(3, c) = outarr(3, c) + pPoints
                    Case "TH"
                       pPoints = 0.5
                       outarr(4, c) = outarr(4, c) + pPoints
                    Case "L"
                       pPoints = 0.5
                       outarr(5, c) = outarr(5, c) + pPoints
                End Select
                     
                If nOK >= 30 Then nPts = nPts + 1
                nOK = 0
                        
              Else
              
                 nOK = nOK + 1
                 If nOK >= 30 Then
                          nPts = nPts + 1
                          nOK = 0
                  End If
                
              End If
            
        Next r
        
    If nOK >= 30 Then
       nPts = nPts + 1
       nOK = 0
    End If
    
    outarr(7, c) = nPts
    
    Next c
    
    
    
 Set outRng = .Range(.Cells(oRow, 1), .Cells(oRow + 6, lastcol))
 
 outRng = outarr
 
 End With
 
   
 

Application.ScreenUpdating = True
   
End Sub

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"?

[jsamp752]

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.

[jsamp752]

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.

[JohnTopley]

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.

If inArr(r, 1) = "" Or inArr(r, 1) > Today Then Exit For

If you want to process all the year

If inArr(r, 1) = "" Then Exit For

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