attendance warning drop off dates and vacation dates

[cuzz]

I track attendance for about 800 employees. If an individual reaches a total of 6 absences ( I have a sum column that adds up the absences) they are placed on a warning for attendance which lasts 3 months and they can not take what we call an emergency vacation day during those 3 months and then only in the next quarter (i.e. 3rd month is May, they can not take a emergency vacation until July 1, which is the start of the next quarter). Now here is where it gets even trickier if they miss 7 days they can not take that emergency vacation for 6 months and then the next quarter, 8 days they can not take that emergency vacation for 9 months and then the next quarter. This is all based on a rolling year meaning you could get 6 absences in November 2005 and then not be able to use an emergency vacation until April 1 of 2006 or worse yet build up a total of 9 absences in November which means the warning falls off in August 2006 but you can not take that emergency vacation until October 1 2006. On the attendance page I physically enter the drop off date of the warning and have conditioning formated the cell to turn green to let me know that the warning has fallen off, but I can not figure our how to link (based on the drop off date) on the vacation page when someone is eligible to use their emergency vacation day since they are different dates. If there is no ways I will have to resolve that I will have to add another column next to the warning drop off date, call it vacation date, condition format it and link it to the vacation page so I will know at a glance when they will be eligible for a emergency day. Sorry for being so long winded, I hope some of this makes sense.

 

[Dutchy]

you may want to investigate the EDATE function

which requires Analysis ToolPak add-in

If you have a date in A1 then =EDATE(A1,9) will give you exactly nine months later.

...or..if you always need the 1st of the month then how about

EOMONTH?

=EOMONTH(A1,9)+1 will give you the 1st January 2006 if A1 contains any date this month

 

[PA HS Teacher]

I understand what you mean by a rolling month in the sense of the warning lasting until 3, 6, 9 months then next quarters etc. from the date of 6th, 7th, or 8th absence.

What I don't understand is if and when the attendance resets? Or is is simply 6,7, or 8 absences within the previous 12 months?

Surely absences 2 years ago don't count. How do you establish the number of absences? Over what dates are you summing?

 

[PA HS Teacher]

I believe you can use a Vlookup to accomplish you objective.

Assuming:

1. You have method for determining the number of absences.
2. You have a method for determining that date you use as a basis for calculating the "Freedom Date". (presumably the most recent absence)
3. You do not set the "Freedom Date" more than 3 quarters from the absence date. (that's why I threw in the max function.)


I used Vlookup in the following manner.
In Cell F2 and copied down:
=VLOOKUP(DATE(YEAR(E2),MONTH(E2)+MIN((D2-5),3)*3,1),$A$1:$B$12,2,1)

Book1
	A	B	C	D	E	F
1	Date	Start of Next Quarter		#Absences	Last Abs Date	Free Again!
2	1/1/2005	4/1/2005		6	11/5/2005	4/1/2006
3	4/1/2005	7/1/2005		7	11/6/2005	7/1/2006
4	7/1/2005	10/1/2005		8	11/7/2005	10/1/2006
5	10/1/2005	1/1/2006		9	11/8/2005	10/1/2006
6	1/1/2006	4/1/2006		10	11/9/2005	10/1/2006
7	4/1/2006	7/1/2006		6	2/5/2005	7/1/2005
8	7/1/2006	10/1/2006		7	2/5/2005	10/1/2005
9	10/1/2006	1/1/2007
10	1/1/2007	4/1/2007
11	4/1/2007	7/1/2007
12	7/1/2007
Sheet1

 

[jumpman]

Are you tracking all 800 people in the same workbook?

 

[cuzz]

1st of all thank you for the great ideas and speedy responses

In response if you go onto a verbal warning (6 sick days) and it falls off in 3 months if you have another absence you go on a written warning for 6 months. Example: 6th sick day is 09/01/05 you are on it for 3 months and can not take an emergency vacation, if you have a 7th sick day and it is during the 3 month period then they tack on another 6 months from the day it happened so now you are into 2006 if no more sick days during that period then you are prorated in 2006 for your sick days (all depends in what quarter your warning falls off). But let's say yopu have your 6th occurrnce on 09/01/05 and the warning drops off on 12/01/05 if you have another sick day before 12/31/05 then you are on that warning from the date of the sick day for 6 months. This is sooooo bad I figure there will be a revolt and the company will need to figure something else out. The only positive thing is if you don't use all of your sick time then the company buys the days back on 12/31 plus gives you 5%

yes all 800 are in 1 excel file with 13 different sheets (pretty big file)

I have decided to manually enter the drop date for the warning and also the date when someone can take an emergency vacation date because the criteria will probably change.

Thanks to all