attendance warning drop off dates and vacation dates

cuzz

New Member
Joined
Jan 18, 2005
Messages
4
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Dutchy

Well-known Member
Joined
Sep 18, 2004
Messages
560
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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
ABCDEF
1DateStart of Next Quarter#AbsencesLast Abs DateFree Again!
21/1/20054/1/2005611/5/20054/1/2006
34/1/20057/1/2005711/6/20057/1/2006
47/1/200510/1/2005811/7/200510/1/2006
510/1/20051/1/2006911/8/200510/1/2006
61/1/20064/1/20061011/9/200510/1/2006
74/1/20067/1/200662/5/20057/1/2005
87/1/200610/1/200672/5/200510/1/2005
910/1/20061/1/2007
101/1/20074/1/2007
114/1/20077/1/2007
127/1/2007
Sheet1
 

cuzz

New Member
Joined
Jan 18, 2005
Messages
4
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. :rolleyes: 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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,560
Messages
5,596,842
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top