+ Reply to Thread
Results 1 to 15 of 15

Attendance Tracking Help

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    8

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    8

    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. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,918

    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: copy to clipboard
    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: copy to clipboard
    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.
    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.

  4. #4
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    8

    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. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,918

    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: copy to clipboard
    Please Login or Register  to view this content.

    2. Amend the formula in G4:AP37 to read:
    Formula: copy to clipboard
    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. #6
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    8

    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. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,918

    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: copy to clipboard
    Please Login or Register  to view this content.

    4. Amend the formula in G1:AP1 to read:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    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. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,918

    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: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    8

    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. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,918

    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. #12
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    8

    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. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,918

    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. #14
    Registered User
    Join Date
    02-05-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    8

    Re: Attendance Tracking Help

    You are correct!

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

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,918

    Re: Attendance Tracking Help

    I believe that the following will work:
    1. Change the formula for column E to read:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help with attendance tracking
    By ttran234 in forum Excel General
    Replies: 4
    Last Post: 02-22-2019, 12:00 AM
  2. Attendance Tracking 90 Days
    By travis0123456789 in forum Excel General
    Replies: 6
    Last Post: 10-15-2018, 03:39 PM
  3. Attendance Tracking
    By JoeHan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-20-2015, 10:20 AM
  4. Attendance Tracking
    By JoeHan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2015, 11:53 AM
  5. Attendance tracking
    By yogananda.muthaiah in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-13-2012, 07:19 AM
  6. [SOLVED] Attendance tracking
    By yogananda.muthaiah in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-27-2012, 09:32 PM
  7. Help in tracking my students' attendance
    By changokid in forum Excel General
    Replies: 0
    Last Post: 02-11-2005, 03:50 PM

Tags for this Thread

Bookmarks

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