Conditional Formatting for Attendance Tracking

Attached below is a sample of an attendance tracking document I am creating, to replace the current excel document used to track certain instances with attendance. I figured out the layout of the document, but I'm having trouble figuring out the formulas to be used with conditional formatting as a method of "flagging" the employee when he/she meets a certain number of intances in a given category. The layout is simple; if the employee is either absent, clocks in early, clocks out late, or misses a punch, then the number of instances is listed in the document, under the appropriate row. The document tracks thses numbers on a weekly basis. I need the left-most cell for each row to turn red if the employee is above the allowed number of instances. The rules are as follows:

Early Out/Clock in Late(Early Out and Clock in Late are to be treated as combined instances in the formula):

=SUM(_4 week period_)>1

Reads: 2 EO/CIL in a month period

OR

=SUM(_12 month(54 week period)_)>6

Reads: 7 EO/CIL in a 12 month period



Missing Punch :

=SUM(_12 week period_)>1

Reads: 2 Missed Punches in a 3 month period

The absent rule is based on a 12 month period, so that one was easy to figure out. But the listed rules need to work within the period of time from the first instance, i.e. if I clocked in late a day in the 3rd week of the year, my 4 week period (and above 6 in a 12 month period) would start from that perticular point. Please help me figure this out, as I am having some trouble. Thank you.

Originally Posted by XOR LX

But you'd already put that rule in the Conditional Formattting yourself ("The absent rule is based on a 12 month period, so that one was easy to figure out"), so I just added it in to my combined version.

This is your formula for Conditional Formatting which applies to cells A9:A10 (Early/Late):

=OR(SUM($C$9:$BB$10)>6,ISNUMBER(MATCH(2,SUBTOTAL(9,OFFSET($C$9,,ROW(INDIRECT("1:"&COUNTA($C$6:$BB$6)-3))-1,2,4)),0)))

This is your formula for Conditional Formatting which applies to cell A11 (Absent):

=SUM($C$11:$BB$11)>6

This is your formula for Conditional Formatting which applies to cell A12 (Missing Punches):

=ISNUMBER(MATCH(2,SUBTOTAL(9,OFFSET($C$12,,ROW(INDIRECT("1:"&COUNTA($C$6:$BB$6)-3))-1,1,12)),0))

As for rolling on the years, it would increase the complexity quite a bit to adapt this solution to work with more than one worksheet. The alternative would be to extend your dates to the right, which would make things simpler (i.e. you could extend the range in the COUNTA($C$6:$BB$6) part (and obviously also in the simpler SUM formulae) to some appropriate column reference beyond or equal to that which contains your data – though I might add that it’s usually not best Excel practice to set range references equal to whole rows or columns.

I confess though that I'm always slightly surprised to see worksheets arranged this way (i.e. 'landscape') - why didn't you lay it out with the E, L, A, MP, etc. as column headers? Excel is always more user-friendly to the portait layour in my opinion (hence the ratio of 64:1 in its favour in terms of number of cells).

Anyway, whatever you decide, all the best.

Regards

Yeah, I thought as much that it would complicate things, but I figured I would ask. As far as the landscape setup goes, it's pretty much the way the company wants it to be. I'm just following their guidelines.

I thought about what you said to try and get the "12 month rolling" function to work, by extending the range of the equation and adding weeks continuing to the next year. Instead of doing that, I just added a column titled "2012 Rolling" and any values from the previous year that need to be carried over, will just be added to that column. Every week as the attendance is updated, whoever enters the data will just have to reduce number in that column manually, depending on the dates of the instances from the previous year. Extending the range and adding more months would make it more complicated, because of the nature of the "rolling" in the attendance tracking. For example, in 2012 if I had an absence in the 3rd and 20th week of the year, two absences would carry over to 2013. But once I pass that 3rd week in 2013, (regardless of any new absences after the 3rd week of 2012), that absence from the 3rd week in 2012 disappears from the count, and now I've just got the absence from the 20th week of 2012 carried over. It would be way too complicated to figure out how to make tracking like that automatic.

So, I went ahead and updated the spreadsheet to what I assume will be the final format. I tried to change around the formulas to include the new column I added, "2012 Rolling", but I didn't seem to get it right. Would you be able to adjust the formulas one last time, to match this new format?
I'll attach the new document below. Thanks, and if you have any opinion on the function of this new column, please let me know.

Good thought, but I probably should have mentioned the other reason extending past the initial 52 week range might not work. Once this attendance tracking sheet in done and implemented, it will be handed off to managers, leaders, secretaries, etc. based on department. These people might not be experienced with Excel enough to be able to modify the document beyond just entering data. The reason I mention this is because if this becomes a true rolling calendar, columns will have to be deleted weekly, as not to carry data over that doesn't matter anymore, (i.e., any instances in "week 1" of 2012 will no longer be valid once "week 1" in 2013 starts). At this point the person in charge of the worksheet will have to delete "week one" of 2012 and keep all the data from "week 2" 2012 and on. But, it's still a good idea and I will try and push this when I present the tracking sheet upon completion.

I updated the Attendance Tracking worksheet with the new formulas, and extended the range to 2014. The extended range is just to get the idea of "12 month rolling" across, though, and most likely will not be keep that way. What I'm most likely going to do is keep a "13 month representation" of the data with the previous 12 months kept in the sheet, and the current month as the right-most column. Ideally, as the data is updated every week, the person in charge will delete the first column of every week, and add a column at the end every week (or 4 weeks ahead of time for each new month). I would like to know, if it's not too much trouble, could you find out the equations I would use if I was to just implement the "rolling 2012" column? I ask, just in case management feels the other idea might be too complicated for the person in charge.

Also, I noticed something very important with the equations you gave me. If I enter a number greater than the allowed instances for a period of time, the conditional formatting stops working. For example, If I enter "3" missed punches for week 4, the equation doesn't flag, even though it’s above the limit. It's very important that all the equations work for at least “7" instances for each week, as it is possible someone can go above the limit in under a week. This document will only be updated, at the most, weekly.

I'll upload the current updated file, as I did before. Thanks again, I really do appreciate the help.

Originally Posted by XOR LX

Basically this is the same except for that the Conditional Formatting now applies to historical periods, so that in effect it adjusts itself dynamically to check the criteria for up to one year in the past.

On the idea of having separate tabs for each year, what I've thought is that you keep this running tab for ALL years and simply hide it. You can change the references for the Conditional Formatting to look at this hidden tab.

Let me know what you think.

So, just replace the weeks with the starting date for each week? I guess it would be a more accurate way for the person in charge of the form to check a year back from the current date.

I tested out the sheet, entering in data from weeks prior to 51 weeks from “TODAY”, and it doesn’t seem to be working right for any of the rows. Instead of ignoring numbers before 5/20/2012, it sometimes counts them toward the ones past 5/20/2012, and sometimes it doesn’t count the numbers when it should, (i.e. any week past 3/31/12.) For Missed Punches, it seems to always be looking for the anything more than 1 occurrence within 3 months, no matter how old or new the entry, even if it’s past this current months date. It's a great idea, and probably the easiest to maintain, but currently it's not fuctioning right.

I entered in data around where it starts to include numbers from dates that shouldn't apply. For Missed Punches, I entered data representing 2 different instances in which the cell will be flagged. Also, sampling numbers a weeks before the current week, after the current week and on the current week, seems to be messing up too. Here's an attachment with the examples.

Ok, now it seems to be working. That name manager thing is something I've never even worked with before. And yes, I mentioned dates past the current date to account for human error, but it's not important to bother with that. The person inputting the data will just have to recheck everything, just in case something is flagged by accident.

Now, is the "Name Manager" formula for dates something that is going to have to be adjusted at any point whatsoever? If not, is their anyway to make it so any formulas won't have to be updated? This way, I could just have the person in charge hide any of the columns they want and add as much as they want without incident.

If that isn't possible, then what I would most likely have done is just have a 57 week calendar, like the previous version with the MAX forumlas. This way, I'll just have the person in charge delete a month and then add a month, as long as they have enough columns to fill out the preset reference range of 57 weeks.

Originally Posted by XOR LX

Basically the Name Manager allows you to create names for formulas and ranges. So here the Named Range Dates is simply the range of cells A7:DJ7 in the 2013 tab, i.e. the extra row of dates I entered.

Obviously if the day arrives when you will be going beyond 22nd December 2013 (column DJ) then this will need updating. Alternatively, it's also quite straightforward to make it dynamic, so that you won't need to do anything: if you change the formula for Dates to:

=OFFSET('2013'!$A$7,0,0,1,COUNT('2013'!$7:$7))

then, if you highlight this, you'll notice that Excel will put dotted lines around the range in your tab which it is defining, and this should be A7:DJ7, just as previously. The difference is that, whereas before this range was static, the OFFSET part of the formula now makes it dynamic. (In fact, the range responds automatically according to the number of entries in row 7, so, if you extend this further to the right, the Defined Range will increase in size appropriately.)

Regards

Thank you so much! I'll take what I got here, and present it tomorrow. I'll let you know how it goes. Thanks again! You've been a very big help.

I presented what we put together so far, and they seemed to like this most recent version of conditional formatting. So, I am going to stick with using the historical formulas. The only change we made was using the date references as "end week" dates, instead counting them as the beginning of the week. This just meant shifting the weeks over the right by one week. There are just a few more things I need help with before it is finalized.

I need a formula to have the "YTD" column count the most recent 52 weeks.

The conditional formulas need to be changed to accept unlimited entries(employees). I noticed in the formulas that it cuts off at A77 which allows me 11 entries. I would need the formula the be set up for individual employee add-ons.

Also, I tried to drag the cells down, with the conditional formatting to copy the formulas over to other employee entries. But, I noticed the references in the formulas don't change to the appropriate rows. Is there any way to get the references to change when I drag the formatting over, instead of entering it in by hand for each employee record?

Thanks again for your time. This should hopefully be the last of the help I will need. I'll upload the final version of the worksheet so you may look at it and make any changes, if needed.

Thanks a lot. It took a little while, but with the changes to the formula and a few last minute adjustments to the sheet, it's 100% done! Now, I'll let the admins play around with it and test it out. Hopefully it functions properly, and will be implemented soon. Thanks again, I couldn't have figured this out without all your help and patience. I'll keep you posted on how everything goes.

Cheers!

I'm trying to create the same thing but don't have anything set up. I'm not an excel guru but I'm not a beginner either. We have turnstyles that clock our time in/out. How do I pull that information into this? Where do I begin?