Absence Tracking Spreadsheet

[dls61721]

I have a spreadsheet that tracks employee absences and clearance dates for
absences under a no fault type absence policy. The rules state that on the
6th chargeable absence, I must send out a notice to the employee. The 8th
chargeable absence results in a two day suspension, the 9th chargeable
absence results in a four day suspension and the 10th in dismissal. We do
charge half day absences. Our policy states that chargeable absences fall
off in 365 days, but also that if there are no chargeable absences in a 180
day period all chargeable absences fall off. I need a way to make this
easier and calculate the absence count as I have over 500 employees to track
and add approximately 75 entries per day. Some employees have over 125
entries each.

Any suggestions?

Column A = Employee’s name (copied from another list)
Column B = Absence Date (copied from another list)
Column C = (Manually Entered)
0 = Not a chargeable absence
.5 = One half day chargeable absence
1 = One day chargeable absence
1.5 = One and one half day chargeable absences
2 = Two days chargeable absence

Column D = Absence Count (Manually Calculated)

 

[FloMM2]

dls61721,
WOW. This is a lot of information still needed, and a lot enter.
Here is what I got to get you started:
Create a "Summary" sheet to gather data from other sheets in same workbook.
Break down your information to one month at a time. So you will have one
"Summary" sheet and twelve "Data" sheets.
List names in Column A on all sheets.
I set up my as below:
Sheet named "January", cell A1 empty, cell B1 "Chargeable", cell A2 "Name",
cell B2 "Total", cell C2 "Date".
Cells D2 thru AH2 are "1/1/2010" thru "1/31/2010". I did this by putting
1/1/2010 in cell D2. in cell E2 I put formula "=D2 + 1", then copied across
to AH2.
In cell AI2, formula "=COUNTIF(D2:AH2,"<>")". This will total the days on a
sheet.
In cell AI3, formaula "=COUNTIF(D3:AH3,"<>")". This will total the enties
made in month (ex. three days with 0.5 will be 3 not 1.5).
In B3, formula "=SUM(D3:AH3)" this will total days used (ex. three days with
0.5 will be 1.5).
Summary --
Page is setup with Name in cell A2 and employees listed in cells A3 thru
A503.
Column B is for Chargeable < 365. Column C is for Chargeable <180, Column D
is for Chargeable Total. Cell E1 has "Date" cell E2 has "of Entry". Cell F1
has "Today's"
, cell G1 has "Date". Cell H1 has formula, "=NOW()", so everyday it is
opened, a new date will appear. Cell I1 has "Total", cell I2 has "Entries".
Cell J1 has "Number of Days", cell J2 has formula,
"=SUM(January!AI2,February!AF2,March!AI2)" I did only three sheets, so
continue until all twelve months are done.
In cell I3, formula "=SUM(January!AI3,February!AF3, March!AI3)" (only three
sheets done).
In cell E3, enter date of new Chargeable time. In cell G3, formula
"=SUM(H1-E3)", this will tel you how many days have past from the last entry
to today. This will have to be copied down for all employees.
In cell C3, formula "=IF(E3-H1>180,0,D3)".
In cell B3, formula "=IF(ABS(E3-H1)>365,0,C3)".
This should be enough to get you headed in the right direction. This may not
be the easiest way to do this, it is the way that I came up with only.
hth