+ Reply to Thread
Results 1 to 11 of 11

How can I create a point system for attendance, tardies, absents, etc.?

  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    texas
    MS-Off Ver
    Excel 2011 MAC
    Posts
    16

    How can I create a point system for attendance, tardies, absents, etc.?

    I need to create a spreadsheet that will record tardies, excused call-ins and unexcused call-ins for employees.

    For example, one tardy = 0.5 points, one excused call in = 1 point, and one unexcused call in = 3 points.

    I plan to record these figures monthly. If I enter that Amy was tardy 7 times with one unexcused call-in that month, I want Excel to show that she has accumulated 6.5 points [(0.5 x 7) + 3].

    I have attached an example spreadsheet. I'm new to excel so any help is appreciated!! Thank you!

    ABSENT.xlsx
    Last edited by amytr1122; 12-27-2012 at 05:41 PM.

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: How can I create a point system for attendance, tardies, absents, etc.?

    The attached file should work.
    I gave two possible solutions. I suggest using the 2nd solution, as it is more intuitive, and easier to change if needed.
    Attached Files Attached Files
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    10-25-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: How can I create a point system for attendance, tardies, absents, etc.?

    Here's an example of what you want. You will need to change the total formula each time you add a new month.

    ABSENT.xlsx

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: How can I create a point system for attendance, tardies, absents, etc.?

    And yet another possibility...

    - Moo
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-27-2012
    Location
    texas
    MS-Off Ver
    Excel 2011 MAC
    Posts
    16

    Red face Re: How can I create a point system for attendance, tardies, absents, etc.?

    Thank you everyone for your responses! You guys helped a bunch.

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: How can I create a point system for attendance, tardies, absents, etc.?

    You're welcome! Glad to help.
    - Moo

  7. #7
    Registered User
    Join Date
    12-27-2012
    Location
    texas
    MS-Off Ver
    Excel 2011 MAC
    Posts
    16

    Linking pages? How to add/deduct points as criteria met?

    I have a few more questions!!

    How do I come up with a rolling system to add and deduct the points as their attendance changes?
    The goal of each employee is to have the least amount of points as possible, because he/she will be penalized once they receive a certain amount of points.
    Let's say that Jane was hired in October. She has accumulated 10 points by December from tardies, call-ins, etc. By the end of January, she has perfect attendance; she is awarded half a point to be taken off toward her 10, giving her 9.5. In February, there is a day she does not show up, so now her total goes up to 12.5 (3 pointed added because of NCNS-no show no call). In March and April, she has perfect attendance, so now her total is 11.5. (half a point allowed for each month with perfect attendance)

    By rolling, I mean a formula that will calculate continuously over month after month, year after year.

    THANK YOU!!!!!
    Attached Files Attached Files
    Last edited by amytr1122; 01-02-2013 at 05:43 PM.

  8. #8
    Registered User
    Join Date
    03-15-2013
    Location
    20166
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Linking pages? How to add/deduct points as criteria met?

    Quote Originally Posted by amytr1122 View Post
    I have a few more questions!!

    How do I come up with a rolling system to add and deduct the points as their attendance changes?
    The goal of each employee is to have the least amount of points as possible, because he/she will be penalized once they receive a certain amount of points.
    Let's say that Jane was hired in October. She has accumulated 10 points by December from tardies, call-ins, etc. By the end of January, she has perfect attendance; she is awarded half a point to be taken off toward her 10, giving her 9.5. In February, there is a day she does not show up, so now her total goes up to 12.5 (3 pointed added because of NCNS-no show no call). In March and April, she has perfect attendance, so now her total is 11.5. (half a point allowed for each month with perfect attendance)

    By rolling, I mean a formula that will calculate continuously over month after month, year after year.

    THANK YOU!!!!!
    Just curious, were you able to find a solution? I'm looking for the same thing, so would love to find out.

  9. #9
    Registered User
    Join Date
    02-12-2015
    Location
    Malaysia
    MS-Off Ver
    XP
    Posts
    1

    Re: How can I create a point system for attendance, tardies, absents, etc.?

    How to ctreate a template base on the range point & calculate the accumulative point from Jan until dec?

    Example: the entire platform will be given 1 point if meeting the target from day 1 until 6 accumulatively. 5 points will be given if meeting the target until 7 to 18 days. There are no point if any day not meeting the target & later, the point system will reset to 0 again.

  10. #10
    Registered User
    Join Date
    07-20-2018
    Location
    Shenandoah Virginia
    MS-Off Ver
    2013
    Posts
    2

    Re: How can I create a point system for attendance, tardies, absents, etc.?

    Question. How can you create an attendance sheet that will apply points based on reason (Ex: Tardy=0.5, Call In=1.0, No Call/No Show=2.0, Vacation=0.0; etc.) plus subtract points, if applied, if a person works a certain amount of days perfect attendance?

    My thought process is to either do a continuous spreadsheet or separate based on month in different workbooks but maybe have a main workbook linked to the monthly workbooks with a listing of each employee's names with their total points.

    I know this would work better in Access but I was wondering if Excel could be set up to do this?

    Thanks.

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,079

    Re: How can I create a point system for attendance, tardies, absents, etc.?

    cjroper111,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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