+ Reply to Thread
Results 1 to 20 of 20

Remove points after certain period of time

  1. #1
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Remove points after certain period of time

    I have a spreadsheet that has 3 sheets - the 1st sheet lists all the employee names and how many points they have; the 2nd sheet shows their names (which it pulls from the 1st sheet), shows the total points but also has a calendar from 1/1/15 to 12/31/15 and the 3rd sheet is just for data. On the 2nd sheet, the purpose is to record if someone calls in, is late or leaves early. If they call in it's 2 points and 1 point for leaving early or coming in late, which is calculated on the 2nd sheet.

    I've been trying to get some help and was told VBA would be my best bet. What I want to happen is that, after 1 pay period (14 days) if a person doesn't call in, isn't late or doesn't leave early, they get 2 points back. They can go as low as -5 points, essentially "banking" points until they call in, are late or leave early.

    I've attached a copy of my current spreadsheet, I just can't figure out a formula that would make it so that after every "pay day" (or 2 week period), the spreadsheet would check and see if people called in or not and then if they didn't, it'd give them all 2 points back up to -5 points.

    Anyone have any thoughts or suggestions?

    Here's my spreadsheet:

    attendancetracker.xlsx

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Remove points after certain period of time

    Hello kwood41799,

    Can you provide a list of pay dates?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    Here are the dates. I've put them in code brackets:

    Please Login or Register  to view this content.
    If you need any more information please let me know. Thank you!
    Last edited by kwood41799; 05-27-2015 at 04:23 PM.

  4. #4
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    Any thoughts?

  5. #5
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    Quote Originally Posted by kwood41799 View Post
    Any thoughts?
    I was actually working on something for you. Might be a couple of hours. I'm multitasking.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    Okay check this for accuracy. We can run the macro in different ways. We can run it when the workbook opens, we can run it when you select the year to date sheet etc.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    This is awesome - it's EXACTLY what I needed! I like that you have the button to update points. How would I make it so the points update when the spreadsheet is opened as well, or does it already do that? Is there something I can put in the "code" so that, starting April 15th, everyone has 0 points and then they accumulate, or subtract, from that point? I only ask because we had changed our policy recently and everyone went back to 0 points on April 15th, 2015. If not I'm sure I can figure out something.

    Again thank you so much. This is exactly what I needed. It's awesome!

  8. #8
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    The version below will run when the workbook is opened.
    With the April 15 issue if you just start your data on 2015 sheet on April 15 and change your pay dates on the pay date sheet you should be okay.

    If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.


    If you are happy with my help, please consider clicking the add reputation button in the lower left hand corner of this post.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    Thank you again for all your help - you have NO idea how much it means to me. I've been trying to find a solution for a long time and you were the only one to finally answer it for me!

    How do I add the button back in so that it updates when the spreadsheet is opened but also updates if they click the button?
    Last edited by kwood41799; 05-28-2015 at 12:00 PM.

  10. #10
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    How do I add the button back in so that it updates when the spreadsheet is opened but also updates if they click the button?
    It's easier to just do it for you. I should have just left it there.

    Thanks for the rep. points and for marking the thread as solved.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    No thank you - you've helped me so much. This is going to make a LOT of people happy

    I'm working on setting it up so it starts counting from 4/15 so people aren't starting with -5 when I add their name.

    Thanks again!
    Last edited by kwood41799; 05-28-2015 at 12:20 PM.

  12. #12
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    Hmm ... so for getting points to 0 and starting from 4/15 - should I just delete the columns from January to April and then remove the dates from the PayPeriods sheet or do I need to do more than that? I keep getting #REF errors. Sorry I'm really unskilled at this lol -- I really really appreciate your help!

  13. #13
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    Quote Originally Posted by kwood41799 View Post
    Hmm ... so for getting points to 0 and starting from 4/15 - should I just delete the columns from January to April and then remove the dates from the PayPeriods sheet or do I need to do more than that? I keep getting #REF errors. Sorry I'm really unskilled at this lol -- I really really appreciate your help!
    Not a problem, post the sheet with the ref errors so I can see what they are.

  14. #14
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    Just another note. I can't predict every possibility and my understanding of your data and pay dates may be incorrect.

    So enter data and check the numbers manually, keep an eye on things, there may be circumstances where you get errors, if you do let me know.

    It's not always possible to predict all problems that may arise. When writing the code I used my limited experience to try and foresee problems that may come up.

    You coming back with issues, is expected, so please don't let it worry you.
    Last edited by skywriter; 05-28-2015 at 02:22 PM.

  15. #15
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    Here you go - thanks again. Just to start, I just deleted columns from 4/14 back to 1/1 and pay periods and when I click "Calculate Points" I get a Type Mismatch error.

    attendancetracker.xlsm

  16. #16
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    Quote Originally Posted by kwood41799 View Post
    Here you go - thanks again. Just to start, I just deleted columns from 4/14 back to 1/1 and pay periods and when I click "Calculate Points" I get a Type Mismatch error.

    Attachment 397766
    Make sure you look at post #14 in case you missed it.

    The #REF! error is a formula issue, nothing to do with the code, except the code is failing because there are no dates in row 5.

    Originally you had the date 1/1/2015 in cell F5 and then all the other dates were created by a formula that just added 1 to the column next to it.

    So you need to enter a date in the F5, but you have another issue because the code failed it stopped automatic calculations.

    If you ever find that your sheets are not calculating the problem is probably calculations are set to manual.

    Your profile says you have the same version of Excel as I do, so do this.

    Put the date in F5 first and then go to the File tab on the Ribbon, then choose options, formulas and at the top it says Calculation Options, you will probably see Manual is selected, change that to Automatic and at the bottom click okay, now all the #Ref errors should be gone.

    On the PayPeriods sheet move those pay periods up to A1, if you delete pay periods move them to the top, if you add, just add them to the bottom.

    They just need to start at A1 and be in order with no blank cells between them.
    Last edited by skywriter; 05-28-2015 at 02:37 PM.

  17. #17
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    I've updated what you said and that worked for fixing the errors and the calculations are working however if I don't enter any data and even put a 0 (and even if I don't) in the 12 Mos Points field on the Year-To-Date-Summary tab and then click calculate, it defaults to -5. I know how to get into the code and know a bit of coding (but obviously not enough to do this lol) so if I need to add or change something, let me know.


    Here's the spreadsheet as it stands:

    attendancetracker (1).xlsm

  18. #18
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    It's not defaulting, it's calculating. We are at the end of May and he hasn't called in, left early etc. during that whole time, since April. Put in some data and it will change.

  19. #19
    Registered User
    Join Date
    04-15-2015
    Location
    minneapolis, mn
    MS-Off Ver
    2010
    Posts
    36

    Re: Remove points after certain period of time

    Whoops - that makes complete sense. After discussing the spreadsheet with our staffing person, they said it's actually every 2 pay periods that points are reduced. Where in the VBA code would I change it so that it's every 2 pay periods instead of just 1 (eg, every 4 weeks)?

    Thanks again for all your help. You're awesome!

    Is it as simple as changing j = j + 1 to j = j + 2?
    Last edited by kwood41799; 05-28-2015 at 04:35 PM.

  20. #20
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Remove points after certain period of time

    I would just remove every other pay period from the pay periods sheet.

+ 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. Remove points after period of time.
    By kwood41799 in forum Excel General
    Replies: 9
    Last Post: 05-26-2015, 05:39 PM
  2. 2 line graphs over same time period but with different date points
    By nicholas.jacka in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-05-2013, 10:06 AM
  3. [SOLVED] Complicated:Calculate time period based on no. of units sold + fluctuations within period
    By omaha.crab in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 03:04 AM
  4. Need to remove holidays from a time period
    By bushlad in forum Excel General
    Replies: 0
    Last Post: 04-10-2012, 12:44 PM
  5. If Cell Ends with a Period, Remove Period
    By Ocean Zhang in forum Excel General
    Replies: 2
    Last Post: 08-07-2011, 04:18 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