90 Day Rolling Attendance Tracker

[mbandw]

Hello All,

I cant figure out how to correctly populate my workbook to deduct a value when it passes 90 days. For example, if an employee left early 90 days go and incurred 0.5 points for it, tomorrow that 0.5 points should fall off the total score since its past 90 days.

The total number of points minus any credits or drop offs over 90 days should be in cell DC. The formula I have in there is:

=(SUM(COUNTIF(INDEX($A3:DB3,MAX(1,COLUMN(DB3)-90)):INDEX($A3:DB3,COLUMN(DB3)-1),{"LE","Call_Off","Late","NCNS","Excused"})/{2,1,2,0.5,1000}))+DE3

The formula I have in a "day" cell (it pulls over data from another sheet) is:

=IFERROR(INDEX(Attendance!$E:$E,MATCH(1,INDEX(($A3=Attendance!$A:$A)*(DA$1=Attendance!$C:$C),0),0))&"","")


I cant attach the spreadsheet as its too big to attach here..

I add a column daily as well.

Help!

[dflak]

You could try compressing the spreadsheet and attaching the ZIP file.

Whenever I hear the word "rolling," I think of named dynamic ranges. Suppose your dates are on column A. Then a rolling 90-day range would look like:
=Offset($A$1,90-day-row-1,0,90,1)

The 90-day-row can be found by using =Match(TODAY()-90,A:A,0.

What the offset command tells you is start in Cell $A$1 go down to the 90-day-row - 1 (the reason for the minus 1 is that match starts counting at 1 while offset starts counting at zero). Go right zero columns and from the cell you wind up in, give me a range 90 rows deep and one column wide.

If you have missing dates then you might have to use a helper column =A2>Today()-90 and search for the first occurrence of TRUE.

So suppose you have this range and it's called Rolling_Date and you want to report on the information in column B (one column to the right) you can define that range as =Offset (Rolling_Date,0,1). Which means same range as Rolling_Date but one column to the right.

[mbandw]

I've tried to attach a zip file of an example spreadsheet.

can you add a column and put in what you are talking about? I've never worked with the offset function.

[dflak]

I notice that you are using Office 2010, but the spreadsheet you sent me is an XLS file (old format). Is there a reason for this?

Can I bring it up to the XLSX format? The main reason I want to do this is to be able to use tables.

Tables confer several advantages:

They know how big they are. So if you build formulas against them, you don't have to guess on how many rows to include. The formulas will use exactly the number of rows that the table has.

Also instead of referring to Attendance!E2:E1123 and trying to remember what that is, you can refer to Table_Attendance[Reason for Absence] and intellisense will help you type that in.

Tables also copy down formulas, formats, validations, etc. each time a row is added.

Being able to use tables would make this project go a lot easier. So, before I begin, I need to know if I can convert to XLSX format.

You could probably do with a list of employees and managers so you can do data validation against them and assure consistent spelling. I'll throw that in as an extra bonus.

[mbandw]

Absolutely you can convert it! At this point I want to scrap everything and start new - I have 200 employees on my real sheet and what I have isn't working anyways so do what you do!

[dflak]

Here is a preliminary. I'll explain what I did next week.

On the formulas page, you can fill in the table for reason and for communications. If you do modify these tables, you'll have to refresh the pivot tables for category and communications. I might get that automated for you. Anyway, these two tables drive drop down lists (data validation) to assure consistent spelling of items.

On the attendance sheet, if you want to add a new record, click on Add Row. This will add a row to the table and the cursor will be placed in the first column of that row. You can then use a drop down list to select the name of the person. You can also type in a name manually and you will get an informational message telling you that it's a new name and it will be added to the list.

The same goes for the manager's column.

The communications is a drop down list of the valid types of communications.

The date is a manual entry.

The reason for absence is a drop down list from the table.

The drop down list for the specifics is determined by what you select in the reason for absence. You will only get a list of valid specifics for that reason.

The reason and specifics determine the number of points assessed.

The Within 90 column handles the "rolling 90" part. This column can be hidden.

On the points tally sheet. the number of points within the last 90 days is handled by a SUMIFS formula.

There is a lot of other information on the points tally sheet. I need a better explanation of what this sheet is for and how it is used. I'll probably give you and add row button on this page too.

[mbandw]

Ok wow this works great! (I would've never been able to figure this out....) anyways, we only used the other data on the points tally sheet because I was manually calculating the 90 day roll and to determine perfect attendance. The only other thing that needs to be considered is the credits. The employee gets a .50 credit for every month that they reach goal (manually entered since each employee might not reach it for any given month) and they also get a .50 credit for each month of perfect attendance however there are no credits given once you reach 0.00. I need the tracker to calculate these things in..not sure if there is another way to determine perfect attendance besides listing out everyday like I did?

again, thank you so much for helping me with this!

[mbandw]

Actually, I can just sort the columns on the Attendance Sheet to determine perfect attendance so I don't need the extra data in the points tally sheet. We still need the configuration for the credits though.

[dflak]

Here is how to work in the credits (I am assuming that they are subject to the same 90-day restriction.

To add up credits: Table_Attendance[Credits],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE)

So just subtract this from the points: SUMIFS(Table_Attendance[Points],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE)-SUMIFS(Table_Attendance[Credits],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE)

But wait! We can't let this figure go below zero, so we wrap the whole thing in MAX(Expression,0) meaning use whichever is larger, the calculation or zero.

=MAX(SUMIFS(Table_Attendance[Points],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE)-SUMIFS(Table_Attendance[Credits],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE),0)

[mbandw]

I tried copying and pasting the last formula and it gave me an error. Its saying "the name you entered is not valid" then highlights [@employee] Im assuming because the credit table hasn't been defined (?) I don't know how the heck you did this amazing spreadsheet so...

=MAX(SUMIFS(Table_Attendance[Points],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE)-SUMIFS(Table_Attendance[Credits],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE),0)


The credits don't have to be on the 90 day restriction. They can apply as long as they have a balance above 0.00

[mbandw]

I add the goal credits to the right on the spreadsheet and the total of all goal/perfect attendance credits they've rcvd are in column T (sum of U2 to AN2)

Column T is what should be subtracted from the formula in column B on Points Tally tab but stops when it reaches 0.00

[dflak]

I got the idea of the credits and that they can't go below zero. That changes the formula a bit. I though you said that points minus credits couldn't go below zero.

Based on your explanation of how the credits are computed, I added a formula to compute them on the attendance page: =IFERROR(MAX(INDEX('Points Tally'!T:T,MATCH([@[Employee Name]],'Points Tally'!A:A,0),0),0),0) - this should be the same number for a person all the way down. I display it only because it is in the original data. Now that you explained it to me, I'm not using it in calculations.

The formula says, find the name on the points tally page (the MATCH part), get the row, find the value in column T:T for that row. If you can't find the name, the points total is zero.

Since you are doing the credits on the same line as the employee is on the points tally page, the computation in column B becomes much more simplier: =SUMIFS(Table_Attendance[Points],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE)-MAX(T2,0).

If you are still having an issue with the formula, check to see that Table_Attendance exists. Go to Formulas -> Name Manager. The table should exist on the list along with what it is currently pointing to.

[dflak]

I mentioned tables, so here is an article that should give you an overview of them: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

[mbandw]

You were right when you said that points minus credits cant be below 0.00. When I downloaded the spreadsheet you gave me yesterday with the updated formula, I tried entering another credit and it didn't do anything to column B on the "Points Tally" tab.

I'm sorry I sound so dumb with all this stuff... I'm sure you've already guessed but I'm an excel novice and all self taught so I really appreciate everything. Your explanations are really helping.

I wanted to change the formula in column B on the "Points Tally" tab to subtract whatever is in Column T of that same row but I don't want the total in column B to ever go below zero.

It looks to me like that's how you set it up (?) but when the data in Column T is increased, the total in Column B doesn't decrease (which is the issue).

I never used the credit column in the "Attendance" tab so maybe we can eliminate that?

PS - I did read the article! -thanks!

[mbandw]

I think I got it: I put this in column B

=SUMIFS(Table_Attendance[Points],Table_Attendance[Employee Name],Table2[[#This Row],[Employee]],Table_Attendance[Within 90],TRUE)-MAX(-S7,-12)

I modified the "Max" part because it was saying which is greater? the number in column T or 0 and it would always be 0 because all the credits are negative numbers so it wouldn't change column B. So then I changed it to the max number of credits you can get in 1 year which would be -12 because they can receive up to 1 credit per month. When I changed that, it was adding the number in column T to column B so then I made that a negative number and it returned a correct result.

Will this formula continue to work or is my thought process off??

[dflak]

I am confused about the credits.

What MAX(-S7,-12) yields is
If S7 = +2 then 2
if S7 = 0 then 0
if S7 = -2 then -2
if S7 = -15 then -12.

Is this what you want? As long as the credits are less than zero, then this does look like what you want.

BTW: Don't you mean T7 instead of S7.

As far as the credits on the attendance form goes, you can simply remove the column. I just filled it in because it was there. No formulas depend on it.

[mbandw]

Nope its not what I want. I want it to be "if B2=0 then remain at 0"

For example..say I have an employee who is already at 0.00 points. If she reaches goal this month and I put the -.50 in July goal column, I want her total in column B to remain at 0.00 and not give her -.50 points in column b

I tried entering an if formula but it didn't work right still

[dflak]

Let's try a different approach to communicating.

Suppose an employee has 2 points in column B then on successive months gets -0.5 credits each month. What would those monthly totals in B look like? Would it be: 2.0, 1.5, 1.0, 0.5, 0.0, 0.0 ...?

[mbandw]

Yes that's how it should work

[dflak]

OK, then this should work: =MAX((SUMIFS(Table_Attendance[Points],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE)+T3),0)

The SUMIFS(Table_Attendance[Points],Table_Attendance[Employee Name],[@Employee],Table_Attendance[Within 90],TRUE) gets the rolling 90 days of points.

To this we add +T3 since the credits are negative numbers and we want them to reduce the number of points.

I wrapped the whole thing in a MAX statement such that MAX(Points+Credits,0) never gets below zero. I might have one more set of parenthesis than I need, but I want to make sure I am applying the MAX properly.

[mbandw]

ok great! works great! THANK YOU SO MUCH FOR ALL YOUR HELP AGAIN!!! you are a super lifesaver

[dflak]

Aw, shucks 'twern't much Good luck with the rest of the project.