+ Reply to Thread
Results 1 to 20 of 20

Need to track work attendance (absent/tardiness) via rolling 30 day point system.

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Need to track work attendance (absent/tardiness) via rolling six month, but a point will roll off if you go thirty days without another point. So essentially every point will expire in six months automatically, but perfect attendance for thirty days following an incident will cause that incident to roll off.


    Absent = 1 pt
    Tardiness = .5



    www.dropbox[.]com/s/zexoqkj7tuycttq/Attendance.xlsx?dl=0

  2. #2
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    This should do the job I think.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    That seems close but when I clear the names points remain in D7 and D9 and D11.

    When the names and absent/tardy is cleared out .5 points remain in D2,3,4,5,6,8,10,12

    IDK why the formulas are exactly the same, so why would D7 be 0 but D8 be .5?

    Did you see my spreadsheet? I like the idea of having employees separate so that the managers can show the employees their points without disclosing others.

  4. #4
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Please upload the file here (in the form) and not in the dropbox.
    Not anyone may have dropbox.
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  5. #5
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    OK. I think I attached it.
    Attached Files Attached Files

  6. #6
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Hey there,
    It seems to be confusing. I got the point that 1 point is rolled off after 30 days of perfect attendance. However, from your spreadsheet it seems to me that you have many categories for absenteeism like AL, EA, LE, etc. But in your post you just wrote two of them "Absent" and "Tardiness". Can you please be more specific to what will be written in the range H7:NJ7?
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  7. #7
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Hi taylorsm, sorry, I didn't see your workbook before. Dropbox wouldn't agree with me
    I see your dropdown in row 7. I don't know how you would clear them after 30days...maybe a macro?
    Can you add the points into row 8, just under the dropdown?
    If so, you could pretty much use the formula I put in my column D into your row 8 and it would change the points to 0 after 30 days perfect attendance.
    Anyway, bedtime now....hopefully one of these other guys will have it solved before I awake

  8. #8
    Registered User
    Join Date
    06-30-2015
    Location
    Arlington
    MS-Off Ver
    Windows 8
    Posts
    5

    Please help

    Please help


    Attached is a Attendance tracker that I need your help with. I need a column added for excused days but not counted in the formula. Also I need the formula to recognize if I deduct a point or half point and it still be able to add correctly for infractions. is this feasible?
    Attached Files Attached Files
    Last edited by jamesbolden2015; 01-24-2016 at 09:11 PM.

  9. #9
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Please help

    Just insert a new column and copy the formula over, just changing the "NCNS" to "ED" or something and in the Total formula, just make sure the total still only lists the three columns you are wanting to count.

    I think I did what you were looking for.

    No clue what your other question is referring to though. Sorry.


    Anyone have any help for my original question?
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-30-2015
    Location
    Arlington
    MS-Off Ver
    Windows 8
    Posts
    5

    Re: Please help

    When you go to add a late or tardy it adds up correctly by the formula. But I want to be able to deduct for perfect attendance. So If I just go over to the total points column and subtract manually the formula is messed up. Meaning If I add another late or tardy it will not add at all.

    I would like a column for Perfect Attendance that will subtract from the total. If you have 3 points but in 90 days you have perfect attendance then I will give you a 1/2 point reward = 2.5.
    Last edited by jamesbolden2015; 01-25-2016 at 12:32 AM.

  11. #11
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Oh, yeah, then you are in a similar boat as me.

    Maybe have additional column that is for perfect attendance points removed and make it negative points, and add that into your other points. So if you have +1 point and then you have a column where you can enter -1 and it will offset.

    Or maybe change whatever "tardy" to a "tardy roll off" where the formula doesn't count it any more. Does that make sense?

  12. #12
    Registered User
    Join Date
    06-30-2015
    Location
    Arlington
    MS-Off Ver
    Windows 8
    Posts
    5

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Taylor, honestly I am lost. I have until Feb 1st. And I have no clue.


    Could you add it into a column like you did the ED

  13. #13
    Registered User
    Join Date
    06-30-2015
    Location
    Arlington
    MS-Off Ver
    Windows 8
    Posts
    5

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    how do I make it for negative points. Can I use the same formula but use negative values instead?

  14. #14
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Hi taylorsm, I didn't get an answer as to whether you can use row 8 to do calculate your points. If so I'll work something out for you, otherwise let me know. I see you have a drop-down in rows 7 to 22 but only 1 name...will they be used for something or was that for a previous idea?
    ...going out this evening, but will be back in a few hours... Look forward to a some further info

  15. #15
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Hey sorry, I have uploaded what is actually my latest file. I am at the office now, so this is what I have going for me now.

    I can do whatever I need to do, as long as it is idiot proof to read. This will be given to managers to keep track of their teams and so it needs to be easy to read/understand.

    Boxes outside of the one row will be empty. Each team member will be on their own page for privacy reasons.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Please help

    Quote Originally Posted by jamesbolden2015 View Post
    When you go to add a late or tardy it adds up correctly by the formula. But I want to be able to deduct for perfect attendance. So If I just go over to the total points column and subtract manually the formula is messed up. Meaning If I add another late or tardy it will not add at all.

    I would like a column for Perfect Attendance that will subtract from the total. If you have 3 points but in 90 days you have perfect attendance then I will give you a 1/2 point reward = 2.5.
    I am at work so I can't do much more, but you could do something like this. Just enter -1,-2 or whatever into the PA (perfect attendance) box and the formula will adjust.
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    Kool, sounds good taylorsm. Unfortunately I am just heading to bed as I have to attend Australia Day functions in the morning, but I will return with a "hopeful" solution after lunch. Give me 13 hours... sorry.. stupid time zones

    I have a solution in mind to easily work with work current sheets, and 2 or 3 others that might give you a nicer looking layout for the managers. I will post what I can as soon as I can

  18. #18
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    A quick question. The 30 days...are they 30 days calendar-wise or are they 30 working days (Mon-Fri only)?

    Edit: Added file.

    If the 30 days are consecutive, including weekends and holidays, then this should work.
    I created a vlookup table for the points in A1:C4.
    The formulas are in row 8. B12 has =today in it, but you can change that to simulate different days throughout the year to test that it works.
    Once you are confident that it does, you can replace that cell reference with Today() in the formulas and delete that cell.
    I have only made changes to the N.Davidson sheet.
    Attached Files Attached Files
    Last edited by Beamernsw; 01-26-2016 at 06:23 AM.

  19. #19
    Registered User
    Join Date
    03-25-2016
    Location
    Milton FL
    MS-Off Ver
    2003
    Posts
    3

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    @ beamernsw how would I copy the N.Davidson sheet and make copies and just change the name instead of editing each page for each employee?

    also is there a way to make it that if a employee goes 30 days without any days missed or late to add a point back to them?>
    Last edited by KuikViper; 05-11-2016 at 02:26 PM.

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

    Re: Need to track work attendance (absent/tardiness) via rolling 30 day point system.

    KuikViper,
    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)

Similar Threads

  1. Track attendance points for a rolling year
    By MN_REV-800 in forum Excel General
    Replies: 17
    Last Post: 02-20-2019, 11:17 PM
  2. Attendance Point System Spreadsheet with Rolling 1-year Points Reversal
    By mfoley9723 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-04-2018, 04:46 PM
  3. Replies: 10
    Last Post: 03-17-2016, 05:34 AM
  4. [SOLVED] Need help for creating a system to track attendance
    By MarkAK19 in forum Excel General
    Replies: 4
    Last Post: 12-18-2015, 08:38 AM
  5. Need Help Rolling 6/12 Month Attendance Point Tracking
    By khintze in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2015, 05:46 PM
  6. create an attendance point system with yearly reversal
    By Ed Terry in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 02-12-2014, 08:04 PM
  7. Keeping track of attendance point system
    By okracerx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2013, 11:08 PM

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