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.