[Solved] Need help and advice with attendance tracker

Discuss the spreadsheet application
Post Reply
swmwshrk
Posts: 22
Joined: Fri Aug 15, 2014 3:56 am

[Solved] Need help and advice with attendance tracker

Post by swmwshrk »

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.
Last edited by robleyd on Tue Sep 18, 2018 1:35 am, edited 2 times in total.
Reason: Added green tick [robleyd, Moderator]
I am very new to excel/calc. What I do know so far is self taught and very limited, I still think formulas are a form of black magic.Please give me step by step instructions, and explain abbreviations. Thank you for your help

Win 7 pro Open Office 4.1.3
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Need help and advice with attendance tracker

Post by 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Need help and advice with attendance tracker

Post by 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
swmwshrk
Posts: 22
Joined: Fri Aug 15, 2014 3:56 am

Re: Need help and advice with attendance tracker

Post by 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.
Attachments
Attendance Tracker Test.ods
(18.57 KiB) Downloaded 124 times
I am very new to excel/calc. What I do know so far is self taught and very limited, I still think formulas are a form of black magic.Please give me step by step instructions, and explain abbreviations. Thank you for your help

Win 7 pro Open Office 4.1.3
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Need help and advice with attendance tracker

Post by 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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Need help and advice with attendance tracker

Post by 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.
Attachments
Attendance_fjcc.ods
(24.28 KiB) Downloaded 120 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
swmwshrk
Posts: 22
Joined: Fri Aug 15, 2014 3:56 am

Re: Need help and advice with attendance tracker

Post by swmwshrk »

FJCC wrote: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.
Image

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?
I am very new to excel/calc. What I do know so far is self taught and very limited, I still think formulas are a form of black magic.Please give me step by step instructions, and explain abbreviations. Thank you for your help

Win 7 pro Open Office 4.1.3
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Need help and advice with attendance tracker

Post by 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post Reply