# Attendance Tracking Help

1. ## Attendance Tracking Help

Hello all!

I'm in the process redoing an attendance tracking spreadsheet for my company and have one into one issue. First, let me run through how we use our point system.
Call in- 1 pt
No call no show(NCNS)- 5 pts
Left early (worked at least half shift) - 0 pts
Left early (worked less than half shift)- 0.5 pts
Excused absence- 0 pts
If an employee goes 6 weeks (42 days) without an absence their point total goes down 1 point. I am wondering what formula I could use so that it calculates the "perfect attendance" points based on today's date if they have had no more absences or the date of the employee's last absence. The problem I previously ran into is if they had gotten 2 points taken off their total for perfect attendance and then called in- it would bring them back to their original total rather than giving them credit for the 12 weeks of going without an absence.

I would appreciate any and all help

2. ## Re: Attendance Tracking Help

I will also note that if a person is sick for 2 or more days in a row only the first call in would count and the subsequent points would be "excused".

3. ## Re: Attendance Tracking Help

This proposal adds columns to the table for each person listed on the Totals sheet.
The column headers are added by copying the list from the Totals sheet and pasting transpose.
The columns show the points that have been reduced due to multiples of 42 days attendance between absences and are populated using:
Formula:
`Please Login or Register  to view this content.`

G1:AP1 shows the number of points excused by multiples of 42 days attendance between the last absence and today and is populated using:
Formula:
`Please Login or Register  to view this content.`

G2:AP2 shows the total reduction of points using: =SUM(SUMIF(Table1[DEBBRA],"<="&9.99^307),G1)
G3:AP3 shows the total points using: =MAX(0,SUM(SUMIFS(Table1[[Point]:[Point]],Table1[[Name]:[Name]],G4),G2))
On the Totals sheet the Point Total column is populated using: =INDEX('Day by Day'!G\$3:AP\$3,MATCH(A5,Table1[[#Headers],[DEBBRA]:[STEPHANIE2]],0))
Let us know if you have any questions.

4. ## Re: Attendance Tracking Help

This is fantastic!! Thank you JeteMc for solving my dilemma

Playing devil's advocate here..
Let's say an employee goes on FMLA for 12 weeks and we want the clock to stop on the point deduction..how do you suggest I go about that? My first thought would be to just put EX for that 12 weeks but that wouldn't account for if they've already gone 40 days without an absence.

5. ## Re: Attendance Tracking Help

Assuming that you do not want to count any days marked EX:
1. Amend the formula in G1:AP1 to read:
Formula:
`Please Login or Register  to view this content.`

2. Amend the formula in G4:AP37 to read:
Formula:
`Please Login or Register  to view this content.`

Another option might be to add "FMLA" as a new code in which case you may replace instances of "EX" in each of the formulas with "FMLA".
Let us know if you have any questions.

6. ## Re: Attendance Tracking Help

JeteMc,
Thank you for working on this for me. I tried using the formulas mentioned above and replaced each instance of "EX" with "FMLA" after adding FMLA to the point formula. For some reason it is adding more points in the Total Point cell when someone is on FMLA for an extended period of time.
Ideally I want the formula to stop the 42 day tally if FMLA shows up. So that way when the person gets off FMLA their tally will continue where they left off. I don't want to count FMLA as an absence that goes against their tally.

7. ## Re: Attendance Tracking Help

You're definitely correct.
1. Add FMLA to the list of codes on the Legend sheet
2. Amend the formula in the Sum column to read: =SUM(COUNTIF(C5,{"CI","LEH","LE","NCNS","EX","FMLA"})*{1,0,0.5,5,0,0})
3. Amend the formula in G5:AP37 to read:
Formula:
`Please Login or Register  to view this content.`

4. Amend the formula in G1:AP1 to read:
Formula:
`Please Login or Register  to view this content.`

Selecting cell and using the Evaluate Formula feature note that in KYLIE's has 45 days between "CL" absences 2/4 to 3/21 however 4 of those days are marked "FMLA" and thus not counted. Since only 41 days are counted no points are reduced.
If you change KYLIE's last CL absence to 3/22 then KYLIE's day's between becomes 42 (i.e. 46 - 4) so that one point is reduced.
Likewise selecting cell and using the Evaluate Formula feature note that between KYLIE's last "CL" absence and today there are 47 days and no "FMLA" absences are included, so one point is removed.
Let us know if you have any questions.

8. ## Re: Attendance Tracking Help

You've been so helpful and I hate to continue to ask a million questions but I'm now running into the issue that if someone has an absence that has dropped off and then they get another one it won't count it.
Example: Debbra has a call off 1/22/2019 and 5/10/2019. In since last abs it shows 0 (which is correct), reduction it shows -2 but in absences it shows 0. In G38 Debbra's CI shows the value -2.
It looks like the value in Reduction has to have some sort of cap where it only subtracts as many points as there were previously every 42 days. So the cap would be -1 for this incident.

When I go through evaluate formula I'm thinking that G38 is comparing it to the last absences for Debbra but shouldn't it be comparing it to today's date (A2)?

9. ## Re: Attendance Tracking Help

Good catch. Don't worry about asking questions, I'll be glad to answer if I can.
I modified the formula in G5 to read:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

10. ## Re: Attendance Tracking Help

Back again! This system has been working great and I've been able to work out any minor kinks but I am so stumped with this one!
Let's say today's date is 8/30
Debbra:
CI 1/8
CI 2/14
LE 5/10
LE 8/5
EX 8/6

For some reason in it keeps saying that she is at 0 total points when she should be at 0.5. It looks like in reductions it is saying -4 so any number below that won't really register. It looks like the formula in G5 needs to be modified to account for that but I don't know how to accomplish that.

Any guidance is much appreciated.

11. ## Re: Attendance Tracking Help

In the process of working out kinks the formulas may have been changed from the ones in the file attached to post #9. If that is the case please upload a sample of the current workbook.
Let us know if you have any questions.

12. ## Re: Attendance Tracking Help

Just looked over it and the formulas should all be the same. Most of the little kinks had to do with formatting errors- not errors with the formulas. You should be able to use the file that was attached in post #9.

13. ## Re: Attendance Tracking Help

Let me see if I understand this correctly.
Debbra has a CI on 1/8 which is 1 point.
The next CI is on 2/14 which is another point. Since there were only 37 days between 1/8 and 2/14 the 1 point is added so Debbra now has 2 points.
Debbra has an LE on 5/10 which is half a point. Since there were 85 days between 2/14 and 5/10 both the previous 2 points are voided.
Debbra has an LE on 8/5 which is half a point. Since there were 87 days between 5/10 and 8/5 the previous half point is voided.
Since there are only 25 days between 8/5 and 8/30 Debbra should have half a point in the Total Pts. cell.
Is that correct?

14. ## Re: Attendance Tracking Help

You are correct!

For whatever reason, the 'total pts' for Debbra is saying 0 rather than 0.5

15. ## Re: Attendance Tracking Help

I believe that the following will work:
1. Change the formula for column E to read:
Formula:
`Please Login or Register  to view this content.`

2. Change the formula for G5:AP40 to read: =IF(OR(\$C5="FMLA",\$C5="EX",\$B5<>G\$4),SUM(G4,0),MAX(0,SUM(\$D5,G4)-ROUNDDOWN(\$E5/42,0)))
3. Change the formula for G3:AP3 to read: =INDEX(Table1[DEBBRA],COUNT(Table1[[Date]:[Date]]))
Let us know if you have any questions.

##### 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