[Solved] Need help and advice with attendance tracker

[robleyd]

I was just assigned to be the person at my job who tracks employee attendance. They provide a paper form we are supposed to use. I hate paper, and have recreated the form in calc. Now I want to add some formulas that will automatically calculate attendance occurances. Here is what I need

Item 1-Calculate Attendance Occurances by Month

Column 1 is occurrences by month for last year. (These numbers will be entered manually each January)
a12 is January
a23 is December

Row (c-ag)12 is January of the current year. Row (c-ag)23 is December of the Current Year.

Column C is The first day of each month
Column AG is the thirty first day of the month (for months with 31 days).

What I need for each row is a formula that will calculate points for each month based on the following values
A=0
O=0
S=0
U=1
E=1
L=1
P=1/3
N=1 (but two consecutive N's=10) N is no call/no show. Company policy is two consecutive NCNS and you are automatically terminated, otherwise it's one occurrence.

So if an employee has 1N 2U 1L during the month of January, I need a value of 4 to appear in cell AH12

Item 2 Calculate total points over a rolling 12 months
Currently I have this set up as a simple =Sum formula for each month, (aj17 is Feb2017 to Jan 2018, aj24 is Jan to Dec 2018). I want a cleaner look, and for this value to automatically populate based on the current month.

So if the current month is October 2018, I need a formula that will give me =sum(a22:a23;ah12:ah21) but on Nov 1, 2018 I need the formula to change to
=sum(a23;ah12:ah22). Cell AJ9 has =today() formatted as MMMM YYYY to pull the date from if needed. This total will be in cell AH24.

Item 3 Conditional formatting and if then for certain values

If the value returned in cell AH24 is 4, 5, or 6 I need the cells AH24 and AJ 24 to turn yellow and the word Written Warning appear in AJ24. I would also like the Tab for that sheet to turn Yellow.

If the Value in cell ah24 is 7,8 or 9, I need the cells AH24 and AJ24 to turn Orange and the word Final Warning appear in AJ24. I would also like the Tab for that sheet to turn Orange.

If the value in cell ah24 is 10 or more, I need the cells AH24 and AJ24 to turn Red and the word Terminate appear in AJ24. I would also like the Tab for that sheet to turn Red.

[RusselB]

For Item 1, I'm going to suggest a lookup table, with VLOOKUP being the first option that comes to mind.
Regarding the 2nd item. I suspect that SUMPRODUCT will be of tremendous use, as it allows for lots of possibilities that would otherwise require multiple formulas.
As to item 3, changing the colour and getting the words is easy, using the Conditional Formatting options, however, I am unaware of any method of programmatically changing the colours of the tabs.

If you want/need more help, please upload a sample of your current spreadsheet so that there's no guess work regarding the layout of the data.

[FJCC]

I suggest that you don't lay out the data in the same way that it is laid out on the paper. It is almost always better to have one column for each variable. In this case that would be Date, Attendance type, Score and, possibly, employee name. A pivot table could then easily give you monthly scores. A twelve month sum could be easily done with SUMIF() and conditional formatting could be applied to the cell containing that formula.
As mentioned earlier, example data would really help with providing concrete advice.

[swmwshrk]

FJCC

This form is supposed to be kept in a binder and is reviewed by the District manager and district Loss prevention manager everytime they visit the store. It has to be extremely similar to the paper form. What I can do is layout the data in non printing cells and just use =pq12 to pull the data into the printable form.

Here is the form I created. I hide all cells I am not using.

I put comments in the cells I need help with.

I also changed the layout slightly so the cells may not match my original post.

[jrkrideau]

After looking at that layout, my advice is don't do it. It is a nightmare and designed to produce errors.

Others may well disagree.

My thoughts are that if you are new to spreadsheets, do not waste time learning more about them, at least, for this task.

Consider using a using a data base (AOO comes with one--See Base) and proper forms to approach the problem. This is exactly the type of job that a data base is designed for.

Learning how to set up a data base takes longer than cobbling together a spreadsheet but it is a lot better in many ways including being a lot less error prone.

With a good report generator you should be able to repeat the hand-printed form perfectly. A data base also allows you to run any number of special reports which is impossible with a spreadsheet.

I, basically, am just agreeing with FJCC that "you don't lay out the data in the same way that it is laid out on the paper" and extending that to suggest that a spreadsheet is just not the right tool for the job.

[FJCC]

I agree a database is a better solution. However, I still put this together. I copied the data into A41:C405 and I used formulas in C13:AG17 to copy the Attendance codes into the display region. The Monthly Occurrences in AH13:AH17 are just SUMIF() formulas. Notice that I filled B41:B405 with empty strings (="") when an attendance code is not entered to keep the display region from looking ugly.

[swmwshrk]

I agree a database is a better solution. However, I still put this together. I copied the data into A41:C405 and I used formulas in C13:AG17 to copy the Attendance codes into the display region. The Monthly Occurrences in AH13:AH17 are just SUMIF() formulas. Notice that I filled B41:B405 with empty strings (="") when an attendance code is not entered to keep the display region from looking ugly.

I didn't quite understand what you did or how you did it.


But I figured out how to use it and to get the results I need. It works perfectly. Thank you so much.

I do have another question.

Cell Range A1-AY39 is set as the printable range. I need one sheet per employee (60-80 people through the new year), and plan on using one sheet as a template to copy as needed for each employee. Is there a way to keep the print range, or will I need to reset it every time I copy the template sheet?

[FJCC]

I don't work with print ranges and poking around a bit did not reveal a way to copy a sheet with its print ranges. I guess you will have to define one for every new sheet.
I encourage you to work through the formulas I used and understand them. It is risky to just copy formulas. You can ask here if you get stuck.