+ Reply to Thread
Results 1 to 62 of 62

Employee Attendance on Rolling 6 Months

  1. #1
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Employee Attendance on Rolling 6 Months

    Good morning,

    I am new to the group as well as the Excel System. I am trying to come up with a spreadsheet that will track our employees attendance on a 6 month rolling attendance calendar. I am having a hard time getting the attendance points to roll off after 6 months. I'm tracking attendance by day, week and month. If an employee has perfect attendance for 30 straight days, then a specific value is entered in that month. What formatting can I use to highlight cells as long as an employee has perfect attendance? Example, perfect attendance in May, June, July all 3 months will be highlighted. If August is NOT a perfect attendance month, then prior months will no longer be highlighted. Also if they have perfect attendance for 30 straight days, the OLDEST point will fall off. Is there a formula I can use to make this happen? I want to set it up so that perfect attendance is noted by a trophy symbol (% in Webdings). How do I set the formatting so that it highlights all cells with that symbol, and then if a month is missed, then all prior highlights go away?

    I hope I am not trying to do too much :-). I need to be able to show each agent there progress at will without showing them all the other agents history. Any suggestions on how to do this? Attached are copies of the first two weeks of data since we've been tracking this information. Any help would be great!!!!

  2. #2
    Registered User
    Join Date
    06-05-2014
    Posts
    13

    Re: Employee Attendance on Rolling 6 Months

    You could try the SUMIF statement. The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

    Hope this helps

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    Do you have seperate files for each week? If so, I suggest you rethink your layout, and have all data in the same workbook, in the SAME worksheet. This will make all sorts of summaries and analysis so much easier
    (and it is generally better to have sheets grow downwards over time, rather than across - ie, have dates going down, names going across)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Thank you for the input. FDibbins, I will redo the layout as you suggested and attach it for input if you don't mind. Thanks again.

  5. #5
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Ok I have redone the workbook, but I am unsure how to input my information to achieve the results I'm looking to accomplish. Any ideas?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-10-2014
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Employee Attendance on Rolling 6 Months

    allocate a code number or text for all the options available
    like below;

    shift 1 - 1
    shift 2 - 2
    absent - A


    then use COUNTIF formula to count number of times each options happend for a week. once the count available it will be easy for applying conditional formating.

    thank you

  7. #7
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Ok I think I'm almost there, but I'm still having some trouble with the formulas. I understand the need to use COUNTIFS and the TODAY formula to help with totals and keep up with the 6 months, but I keep getting error messages (i.e err 508). I attached the most recent copy with a key to the far right showing codes and amount of points they carry. I am having a hard time getting the attendance points to roll off after 6 months. Any help would be greatly appreciated. If an employee has perfect attendance for 30 straight days, then a specific value is entered in that month. What formatting can I use to highlight cells as long as an employee has perfect attendance? Example, perfect attendance in May, June, July all 3 months will be highlighted. If August is NOT a perfect attendance month, then prior months will no longer be highlighted. Also if they have perfect attendance for 30 straight days, the OLDEST point will fall off. Is there a formula I can use to make this happen? I want to set it up so that perfect attendance is noted by a trophy symbol (% in Webdings). How do I set the formatting so that it highlights all cells with that symbol, and then if a month is missed, then all prior highlights go away?

  8. #8
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Can anyone help me delete this post so that I can post it to the Commercial Services Subforum? I tried to send a PM to one of the administrators and I don't think it sent. I can't find any trace of it in my sent box.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    I can close this if you want. But perhaps if you showed me a few examples (created manually), of what you want 30 days (30 calendar days, or 30 work days, or is it 1 month?) to look like, and what 3 months to look like (same question here---90 calendar days, 90 work days, 3 months?)

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Employee Attendance on Rolling 6 Months

    I got your message, Lesiahawk, it was sent. But it appears this thread is still active.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  11. #11
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Thanks JBeaucaire. I will resend the request again if I need to. FDibbins may be able to help me. I want to leave it open for the time being. Thanks again!!!

  12. #12
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Ok FDibbins, sorry for the delay. I think this will help you better see what I am trying to accomplish. For example: As long as an agent has perfect attendance the cells will be highlighted purple. Once the agent hits a 30 calendar day mark with perfect attendance, the cell will highlight orange and the cell containing the oldest point will highlight orange causing the 6 month total to drop one point (without going negative). As long as the agent has ABSOLUTE perfect attendance for 180 calendar days, the 6 month totals bar will keep a "trophy". Once the agent breaks their perfect attendance, the totals bar will reflect the correct number of points and highlight peach. To the right of the spreadsheet is a key. I am trying to make this as easy as possible to keep up with without having to purchase an actual software (unfortunately I cannot get funds to do that). Please let me know if this helps you and if this is something that I would be able to accomplish. I hope I explained it all clearly.

    Thanks in advance!!!

    Lesia

  13. #13
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    FDibbins, any ideas?

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    There may be a better way to do this, but I cannot think of it at the moment.

    My suggestion is to have a small table with all codes that represent an "authorised" attendance - a shift type that will count towards the 30 day/6 month score. I gave this range a range name of GoodShifts, for ease of use

    Then insert a helper column after each name (this can be hidden), and copy this down...
    =IF(ISNUMBER(MATCH(B3,GoodShifts,0)),C2+1,0)
    This will accululate/sum work shifts and reset when a "good shift" is not found

    If this is something you can work with, we can take it further and start to ID the 30-day ee's

    See the attached for what I have so far

  15. #15
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Talking Re: Employee Attendance on Rolling 6 Months

    FDibbins, THIS IS AWESOME!!!!! Thanks!! Let me know what I need to do to help you further assist me!!!! THANKS AGAIN!!!!!

  16. #16
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Please forgive my ignorance, but could you also tell me in laymans terms what this formula says piece by piece (=IF(ISNUMBER(MATCH(B3,GoodShifts,0)),C2+1,0))? I understand what it is doing, but I don't understand how to break it down in order to explain it to another person and use it in future spreadsheets.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    Great

    OK for the next step...
    1. I presume that row 33 and 34 were just rows you inserted to show what you wanted? Or will these rows always be there?
    2. In the table I put together (GoodShifts), are there more codes to add to that?
    3. How would you want the results presented/displayed? Could you "mock-up" an example of what/how you want to see this?

  18. #18
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    That is correct, row 33 and 34 are not necessities, but I will need a row that keeps a tally of the total number of points an employees has accrued. Authorized attendance codes would include EH, SO, X and XX. Could you tell me how you told the system what a "GOODSHIFT" was and how you grouped them together? (I couldn't tell from the spreadsheet). I will go ahead and fill in the formula for the remaining employees and and repost tomorrow. Thanks again!!!!

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    To respond to post 16...

    =IF(ISNUMBER(MATCH(B3,GoodShifts,0)),C2+1,0)

    Generally with complex formulas, you work from the inside out.
    The 1st thing I needed to do was to see if the code in B3 was in the list of "good codes", which I did with...
    MATCH(B3,GoodShifts,0)
    (as I said before, I creatred a named range for those codes - not essential, but it can help to understand what it is doing)
    So, this is seeing if B3 is anwhere in the range of good shifts - if it is, it will return the row it was found in. the actual number is not really of concern, just the fact that it will return a number if found (and an error if not)

    We now have either a value or an error from that, so we just need to test for that...
    ISNUMBER(MATCH(B3,GoodShifts,0))
    we now wrap that into an IF statement...
    =IF(ISNUMBER(MATCH(B3,GoodShifts,0)),C2+1,0)
    so if there is a match...
    take the value from the cell above (C2 in this case, which is empty), and add 1 to it
    If there is no match, then reset the count to 0
    If B3 is a match, C3 = 1
    If B4 is a match, C4 = 2
    If B5 is NOT a match, C5 = 0
    If B6 is a match, C6 = 1
    etc

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    In the attached, I have made a start towards extracting each person's current "score".

    At the top of each helper, I have added a formula to pull in the latest "score, based on today's date...
    =INDEX(C3:C185,MATCH(TODAY()-$A$1,$A$3:$A$185,0))
    The -$A$1 part was included because you only have data up to 7/7 - which would mean that today, everyone would have 0, because there are no entries.

    So I put how many days from TODAY 7/7 is (3), so that the calc will work on the existing data.

    Next, on sheet2, I added a table to pull in all teh names, along with their current "score". We can then probably start working from that, instead of the mass of raw data?

    Let me know what you think please?

  21. #21
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Ok, here is an example of what I'm trying to achieve in the end (somewhat). If you have any better ideas, please run them by me. I took the table you entered and put it on sheet 2. Then added a 2nd row that calculates the total # of points that an employee has to date (there are no formulas built in here). Somewhere I need to build in the rolling 6 months but I haven't quite figured out where that should go. Let me know if this helps.

    Side Note: EVERYTHING IS LOOKING GREAT!!!

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    By changing the formula in E1, for instance, to a manual entry, you remove the capacity to "keep score". If you want that sign to show, we could put that in row 2, instead of row 1? OR, we could make the name cell change comor for different levels?

    Next....
    Explain to me how you arrive at the points in row 2 please?

  23. #23
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    Explain to me how you arrive at the points in row 2 please?
    I don't know how the point score works but Nancy and JOY are about to get FIRED

  24. #24
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    By changing the formula in E1, for instance, to a manual entry, you remove the capacity to "keep score". If you want that sign to show, we could put that in row 2, instead of row 1? OR, we could make the name cell change comor for different levels?

    Next....
    Explain to me how you arrive at the points in row 2 please?
    Making the name cell change colors for different levels would be a great idea. I just thought the trophy would be different. I didn't know if there was a formula that we could use to keep the trophy there as long as the formula contained "goodshifts" As far as the points in row 2: the key to the far right shows how many points each "badshift" carries. I don't know how to figure the countifs to determine what formula to use. For example, Kimberly called out (CO) once so she had 2 points as of 7/9/15. Courtney called out (CO) 7/6/15, 6/20/15, and 6/1/15 then was late (I) 6/315, 6/5,15, and 6/7/15 giving her a total of 9 points as of 7/9/15. Shirley has had perfect attendance since June 1st so her point total is 0 (giving her a trophy). Let me know if this helps.

  25. #25
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Cool Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by BlindAlley View Post
    I don't know how the point score works but Nancy and JOY are about to get FIRED

    Actually, Nancy C has not received a point since 6/6/15 giving her 34 calender days of perfect attendance and Joy 6/5/15 giving her 35 calender days of perfect attendance. They have both lost their oldest point due to meeting the 30 calender day mark putting Nancy C at 1 point and Joy at 0 points.

  26. #26
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    OK, I will take another look for you soon

  27. #27
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    I noticed that you have a trailing space in Courtey's I's, I have removed 2 (left F10, so you can see). You need to make sure that your data entry is accurate, "I " is not the same as "I"

    I have turned sheet2 into a Summary sheet, with a table showing names and "day codes", with total points.
    B1=todays date, this will update each day
    D1=days to go "back"
    F1=the date to go back to

    Let me know what you think?

  28. #28
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    I noticed that you have a trailing space in Courtey's I's, I have removed 2 (left F10, so you can see). You need to make sure that your data entry is accurate, "I " is not the same as "I"

    I have turned sheet2 into a Summary sheet, with a table showing names and "day codes", with total points.
    B1=todays date, this will update each day
    D1=days to go "back"
    F1=the date to go back to

    Let me know what you think?
    Ok, I see what you are saying about Courtney's entries. I will definitely be more careful when entering my data.

    One question: The green highlight, is this supposed to signify perfect attendance? If so, can you tell me what makes up that formula so that I can have a different color for "absolute" perfect attendance (no bad shifts for 6 months (not sure how many calender days that would be)) and "redeemed" perfect attendance (30 days with no bad shifts).

    Other than that, everything looks good.

  29. #29
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    I noticed that you have a trailing space in Courtey's I's, I have removed 2 (left F10, so you can see). You need to make sure that your data entry is accurate, "I " is not the same as "I"

    I have turned sheet2 into a Summary sheet, with a table showing names and "day codes", with total points.
    B1=todays date, this will update each day
    D1=days to go "back"
    F1=the date to go back to

    Let me know what you think?

    One more thing: Explain the formula you used to obtain the points. Some of the agents have accrued more points that are showing. Is it because my data is entered incorrectly?

  30. #30
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by lesiahawk View Post
    One more thing: Explain the formula you used to obtain the points. Some of the agents have accrued more points that are showing. Is it because my data is entered incorrectly?
    Wow, I have a lot of "BAD DATA"! I'm fixing that now.

  31. #31
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    Wow, I have a lot of "BAD DATA"! I'm fixing that now.
    haha ok, that always makes a big difference

    The green highlight, is this supposed to signify perfect attendance?
    Yes that is correct, I did that with Conditional formatting, and it is based on "Latest Score" (Summary, column B) being > 30.
    That table os only pulling in data for the dates from today, backwards for however many days you specify in D1. I think it would probably be better if you had a 2nd table for the 6-month test?

  32. #32
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    haha ok, that always makes a big difference



    Yes that is correct, I did that with Conditional formatting, and it is based on "Latest Score" (Summary, column B) being > 30.
    That table os only pulling in data for the dates from today, backwards for however many days you specify in D1. I think it would probably be better if you had a 2nd table for the 6-month test?
    Ok, so would the 2nd table need to be on a 3rd sheet?

  33. #33
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    The 2nd table could go below the 1st 1 - or it could go on another sheet - your choice?

    (I would need at least 6-8 months of data to test with)

    hmm actually, come to think about it, couldnt the "Latest Score" column give you that info?

  34. #34
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    The 2nd table could go below the 1st 1 - or it could go on another sheet - your choice?

    (I would need at least 6-8 months of data to test with)

    hmm actually, come to think about it, couldnt the "Latest Score" column give you that info?
    I'll put it on a 3rd sheet to be easier on the eyes and yes sir the "Latest Score" column will provide that, I just needed to know how to separate it. I think we're done. Is there's anything else missing?

  35. #35
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by lesiahawk View Post
    I'll put it on a 3rd sheet to be easier on the eyes and yes sir the "Latest Score" column will provide that, I just needed to know how to separate it. I think we're done. Is there's anything else missing?
    I just thought of something else. I have to manually delete the oldest occurance after every rolling 30 or 183 days, is this correct? Or is there a formula that would automatically delete the contents from that cell? (I.E. N8 would still highlight red, but CO would no longer be in the cell for Joy.)

    I tried to copy the summary sheet into sheet 3 and change D1 to 183 days and all of my "latest score" turned to "0". I DON'T KNOW WHAT HAPPENED.

  36. #36
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    In the attached, I have...
    1. Hidden the columns in data Entry (new name for sheet1)
    2. Added a Latest Score sheet for the "Latest Score"
    3. Hidden the "Latest score" column on Points sheet (new name for sheet2). I kept the column, it is being used to color the "perfect points" rows

    If there is anything else you would like, please let me know, this was a fun project

    edit: just saw your post # 35, let me take a look

  37. #37
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    Just so we are on the same page here...

    Im not sure why you would need to delete anything?

    1. the Latest Score will keep track until the have a "Bad shift", and then it will reset back to 0. If they continue to be perfect for the next 10 years, they would then just constantly meet the 6-month requrement

    2. for the points, that is ONLY counting shift-keys (EH/X/XX/etc) for the dates specified, ie starting with TODAY, and going back however many days you enter - anything further back than that, wont be included

  38. #38
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    Just so we are on the same page here...

    Im not sure why you would need to delete anything?

    1. the Latest Score will keep track until the have a "Bad shift", and then it will reset back to 0. If they continue to be perfect for the next 10 years, they would then just constantly meet the 6-month requrement

    2. for the points, that is ONLY counting shift-keys (EH/X/XX/etc) for the dates specified, ie starting with TODAY, and going back however many days you enter - anything further back than that, wont be included
    If an employee gets 30 calender days perfect attendance they will drop the oldest point (bad shift) accrued, as it is with Joy. She reached a 30 day mark which would have allowed her to drop the points she picked up in N8. From what I can tell, none of the formulas will automatically do this or am I missing something?

  39. #39
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    the formula does not exactly "drop" anything, but it only takes data into account between 2 defined dates. So if a bad shift existed on day 31, and you are only pulling data from 30 days back, that would not be included anyway (or am I missing something here?)

  40. #40
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    Just so we are on the same page here...

    Im not sure why you would need to delete anything?

    1. the Latest Score will keep track until the have a "Bad shift", and then it will reset back to 0. If they continue to be perfect for the next 10 years, they would then just constantly meet the 6-month requrement

    2. for the points, that is ONLY counting shift-keys (EH/X/XX/etc) for the dates specified, ie starting with TODAY, and going back however many days you enter - anything further back than that, wont be included
    I also noticed that when I was correcting all of the bad data in the most recent spreadsheet, the point totals were not adjusting to accommodate the fix.......?
    Attached Files Attached Files

  41. #41
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    the formula does not exactly "drop" anything, but it only takes data into account between 2 defined dates. So if a bad shift existed on day 31, and you are only pulling data from 30 days back, that would not be included anyway (or am I missing something here?)
    Ok, that makes sense and that will work but I still don't know why the data in the DATA ENTRY sheet isn't auto correcting? When I noticed that Courtney's point total still said 4 instead of 9 (after the corrections) I didn't adjust any of the rest.

  42. #42
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    You probably made the changes in dates that were outside (before) your start date...the start date is determined by Points sheet D1. It is current set to 30 days - change it to, say, 60, and Cortney becomes 9

  43. #43
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Awesome! Thanks so much for everything!!!

  44. #44
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    Well, it took us a while, but it seems we have got you to where you wanted to be

    Play around with it for a while, and let me know how you make out. In the mean time, if that answers your questions (for now), please mark this thread solved, using the thread tools towards the top of the screen

  45. #45
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Correction: Points fall off after 30 rolling calendar days of perfect attendance and after 183 rolling calendar days (perfect attendance or not). At maximum, an occurrence only has a 6 month life span then it falls off.
    Attached Files Attached Files
    Last edited by lesiahawk; 07-12-2015 at 11:32 PM.

  46. #46
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    I tried to edit post #45 and apparently I have deleted it completely. Here it is again with the edit included.

    Ok, I have played with the Spreadsheet for a little while and I have a few questions. The below formulas are all the ones that we are basically using. I understand exactly what they are doing, but I am not able to break them down so that I can tell someone what they mean (because I don’t know myself). Could you explain them to me like you did the one for =IF(ISNUMBER(MATCH(B3,GoodShifts,0)),C2+1,0)?

    Data Entry Sheet C1 =INDEX(C3:C185,MATCH($A$1,$A$3:$A$185,0))

    Data Entry Sheet B2 =INDEX(Points!$C$4:$C$40,MATCH(B$1,Points!$A$4:$A$40,0))

    Points Sheet I1 =COUNTIF('Data Entry'!A:A,"<="&TODAY())

    Points Sheet D4 =IF($A4="","",COUNTIFS(OFFSET('Data Entry'!$B$3,1,MATCH($A4,'Data Entry'!$B$1:$BQ$1,0)-1,$I$1,1),D$3,OFFSET('Data Entry'!$A$3,1,0,$I$1,1),"<="&$C$1,OFFSET('Data Entry'!$A$3,1,0,$I$1,1),">"&$F$1))

    Points Sheet C4 =IF(A4="","",SUMPRODUCT(($D$3:$P$3='Data Entry'!$BS$4:$BS$16)*('Data Entry'!$BU$4:$BU$16)*(Points!$D4:$P4))

    Latest Score Sheet B4 =IF(A4="","",INDEX('Data Entry'!$B$1:$BQ$1,MATCH(Points!$A4,'Data Entry'!$B$1:$BQ$1,0)+1))

    Also, what are we using on Sheet 3? I understand that you created a table and called it “goodshifts” Where does the spreadsheet pull the data from when you put the words “goodshift” in the formula?

    Could I copy the information from the point’s sheet into a new spreadsheet and simply change D1 to 183 to track 6 month perfect attendance?

    Lastly, points fall off after 30 rolling calendar days of perfect attendance and after 183 rolling calendar days (perfect attendance or not). At maximum, an occurrence only has a 6 month life span then it falls off. With the formulas we’re using, we will see in the “latest score” column on the data entry sheet when an employee reaches 30 days perfect attendance but the “points total” on the data entry sheet will not reflect the loss of a point. The formula, if I’m understanding correctly will only tell you how many points an agent has had in the last 30 days and highlight that agent I the total is zero, not drop the oldest point when that agent has went 30 days without receiving a point. Is there no way that the formula can only calculate points on a rolling calendar day period?

  47. #47
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    Before I start withthe explanation, let me 1st tell you how INDEX/MATCH work. It is a very powerful function combo that can return all sorts of stuff, and forms the basis of many calcs
    INDEX returns a value at the intersect (meeting) of a row and a column.
    INDEX() syntax is =INDEX(range,row-num,column-num)
    so something like =INDEX(C3:C185,43,1)
    will return the contents of C45 (row 43, column 1) Dont forget, we started on row 3, not row 1, so the 43rd row in the range 3:185, is actually row 45 (43+2)
    Didnt even use MATCH, did we?

    But, we did not know what row we needed to use in that range (I cheated, and looked it up). So, for that, we need to use MATCH...
    MATCH syntax is...=match(criteria-to-find,column-to-search-in,0) 0 returns exact match
    =MATCH($A$1,$A$3:$A$185,0)
    If $A$1 contains today's date, then it will look down the range and find the match for TODAY - in this case, it returns 43
    (If we needed to find the column number, we use the same thing, just change the range=MATCH(criteria-to-find,row-to-search-in,0) - inthis case, we dont need the column number, we are only using column A

    Lets start with this 1...
    Data Entry Sheet C1 =INDEX(C3:C185,MATCH($A$1,$A$3:$A$185,0))
    This is identifying which row had TODAY's date, then pulling the "score" from that row for that person
    Remember, I said that most complex formulas are read from the inside/middle out? This is 1 of those.
    MATCH($A$1,$A$3:$A$185,0)
    ...in the range A3"A185, tell me which row contains today's date. This answer will obviously change from day to day
    Once we have that row number, tell me the corresponding value in C3:C185. This will be the current "score" for that person
    Note that in many of my ranges, I use $letter$number, in others just $letter-number, and in others, no $ at all
    $ is a way to anchor/lock/fix a cell ref so it doesnt move when copied. If you need me to explain how that works, let me know, I wont go into it now

    The next 1 is pretty much the same thing...
    Data Entry Sheet B2 =INDEX(Points!$C$4:$C$40,MATCH(B$1,Points!$A$4:$A$40,0))
    to see what this is doing, check what the range and criteria references are/where they are pointing to.

    A very powerful tool to see what a formula is doing, is the "Insert Function"...the Fx butto just to the left of the formula auditing bar. If you click on a formula, then click on that, it will show you/walk you through whatthe formula is doing, what is's syntax is, and what answers if it returning.

    Get comfortable with INDEX/MATCH, and we can move on to the next 1

    Also, what are we using on Sheet 3? I understand that you created a table and called it “goodshifts” Where does the spreadsheet pull the data from when you put the words “goodshift” in the formula?
    sheet3 was there when you uploaded the 1st file. The ONLY thing I am using it for, is for the table of "good shifts" - you can ditch the rest if you dont need it

  48. #48
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    Get comfortable with INDEX/MATCH, and we can move on to the next 1
    Ok, I think I have the basic concept of INDEX/MATCH, what about the others? I also tried to use the "Insert Function" (FX) key but it just gave me something that looked like this and highlighted some of the columns/rows used. Unfortunately, I need a more detailed explanation (like you have provided with INDEX/MATCH!!!!!) Thanks again!!

  49. #49
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    Get comfortable with INDEX/MATCH, and we can move on to the next 1

    Ok, the attendance spreadsheet is coming along very well. Now I am running into errors when I attempt to add new employees. I am unsure how to fix this. We want to keep all the previous employees on the spreadsheet so we simply hide there columns instead of deleting them. Attached is a copy of the most recent spreadsheet.
    Attached Files Attached Files

  50. #50
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    Sorry I missed your post # 48, do you still need help with that?

    Regarding adding new EE's, I had to change the formula on Points D4 (down and across) to increase the range (from BW to DA)
    Because I foresee that you will be adding more names, I moved the "points table" from Data Entry sheet to Sheet 3, to get it out the way (don't want to over-write anything there, do we?)
    I added a new EE in for you, to test, and it all seems to be working OK
    Remember when you add a new EE, you need to include the hidden column for them as well

    Also, I noticed that the formulas I had in Data Entry sheet in rows 1 and 2, had changed from...
    B2=INDEX(Points!$C$4:$C$60,MATCH(B$1,Points!$A$4:$A$60,0))
    C1=INDEX(C3:C185,MATCH($A$1,$A$3:$A$185,0))
    to...
    B2={INDEX(Points!$C$4:$C$60,MATCH(B$1,Points!$A$4:$A$60,0))}
    C1={INDEX(C3:C185,MATCH($A$1,$A$3:$A$185,0))}
    This changes a regular formula to an ARRAY formula, which can be far less efficient. Leave them as they are, they don't need to be ARRAY's

    Let me know how you make out please?
    Attached Files Attached Files

  51. #51
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    Sorry I missed your post # 48, do you still need help with that? Let me know how you make out please?
    Yes please! I still need help with the breakdowns! :-) Thanks! And the additions look great!! I think I might have changed the formula when I was trying to figure out how to add new employees and forgot to change it back!!! I'm sorry!!! Thanks for catching it! :-)

  52. #52
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    Yes please! I still need help with the breakdowns! :-)
    OK, so which 1 do you want to start with?

  53. #53
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    Data Entry Sheet B2 =INDEX(Points!$C$4:$C$40,MATCH(B$1,Points!$A$4:$A$40,0))
    Let's start here and work down. If that's ok with you.

  54. #54
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    =INDEX(Points!$C$4:$C$40,MATCH(B$1,Points!$A$4:$A$40,0))
    I thought you were OK with INDEX/MATCH?

  55. #55
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    I thought you were OK with INDEX/MATCH?
    I am, sorry. I copied the wrong oe. I'm ready for COUNTIF's now.

    Points Sheet I1 =COUNTIF('Data Entry'!A:A,"<="&TODAY())

  56. #56
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    Green Highlights - "Latest Score" (Summary, column B) being > 30.That table os only pulling in data for the dates from today, backwards for however many days you specify in D1.
    Also somehow, I am no longer seeing the green highlights?? This also may have been messed up when I was trying to figure out how to add new EE columns. Please advise.

  57. #57
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee Attendance on Rolling 6 Months

    =COUNTIF('Data Entry'!A:A,"<="&TODAY())
    the COUNTIF() is used to count stuff that meets a specified criteria (SUMIF() adds them up), the syntax is...
    =COUNTIF(range, criteria)

    The criteria can be a specific thing...count all entries that say GREEN
    or it can be a value...count how many times 10 appears
    That is the basic syntax. Once you have that mastered, you can start getting a bit more involved, and start counting all values less than/equal to 10.
    =COUNTIF(range"<=10")
    you will notice that I had to use "<=10" there, and not just <=10. That is because <= (in this context) is text, and when you enter text, you need to wrap it with ""
    If the criteria is another cell (or a formula, like TODAY), then you need to combine the operator and the reference with &, as I did there with TODAY...
    =COUNTIF('Data Entry'!A:A,"<="&TODAY())
    Search all of column A and count how many entries are <= today's date

    Make sense?

    The SUMIF() and AVERAGEIF() work exactly the same way, but you end off with the range you want to add/average

    From excel 2007 they added an improvement to the COUNTIF/SUMIF/AVERAGEIF family by giving them the ability to look at multiple criteria...COUNTIFs/SUMIFs/AVERAGEIFs

    COUNTIFs() syntax justy gets extended to =COUNTIFs(range1,criteria1,range2,ciriteria2,range3................
    SUMIFs/AVERAGEIFs work the exact same way, the only difference being that the sum/average range gets put at the end now
    =SUMIFs(criteria-range1,criteria1,criteria-range2,criteria2,criteria-range3,criteria3...................sum-range)

    I will take a look at you Q in post 56 and get back to you

  58. #58
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    the COUNTIF() is used to count stuff that meets a specified criteria (SUMIF() adds them up), the syntax is...
    =COUNTIF(range, criteria)
    Ok, that makes sense. So looking at

    Points Sheet D4 =IF($A4="","",COUNTIFS(OFFSET('Data Entry'!$B$3,1,MATCH($A4,'Data Entry'!$B$1:$BQ$1,0)-1,$I$1,1),D$3,OFFSET('Data Entry'!$A$3,1,0,$I$1,1),"<="&$C$1,OFFSET('Data Entry'!$A$3,1,0,$I$1,1),">"&$F$1))

    what is the OFFSET? I also don't understand this part $A4="","", of the formula??

  59. #59
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by lesiahawk View Post
    I am no longer seeing the green highlights??
    Hey FDibbins, were you able to figure this out?

  60. #60
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by lesiahawk View Post

    Points Sheet D4 =IF($A4="","",COUNTIFS(OFFSET('Data Entry'!$B$3,1,MATCH($A4,'Data Entry'!$B$1:$BQ$1,0)-1,$I$1,1),D$3,OFFSET('Data Entry'!$A$3,1,0,$I$1,1),"<="&$C$1,OFFSET('Data Entry'!$A$3,1,0,$I$1,1),">"&$F$1))
    What is the OFFSET? I also don't understand this part $A4="","", of the formula??

  61. #61
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    Quote Originally Posted by FDibbins View Post
    I will take a look at you Q in post 56 and get back to you
    Where you able to check this one out for me? I also added a couple other post as well #58, #59 and #60.

    Recently I have noticed that 2 agents {Leah (35) and Shanita (19)} points are not computing correctly and I can't figure out where the miscalculations are happening. They are showing Leah 24 and Shanita 15. Can you help me with that??? The current spreadsheet is to large for me to attach, but the one titled 9-25-15 doesn't reflect Leah's correct total either. It says she has 13 but she really has 26. Please advise.
    Attached Files Attached Files

  62. #62
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    86

    Re: Employee Attendance on Rolling 6 Months

    The additional "goodshifts" are EHT and CI
    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. Attendance on a rolling calendar
    By zarger11 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-30-2015, 01:24 PM
  2. Rolling 365 attendance tracker
    By LotusLL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-16-2015, 05:33 PM
  3. Need Help - AutoCalculate Attendance Points for employee attendance records
    By switzd0d in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-17-2015, 03:00 PM
  4. [SOLVED] 90 rolling attendance tracker
    By Fuhgawz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-21-2013, 02:49 AM
  5. [SOLVED] employee attendance
    By ruth in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-08-2005, 12:06 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